Sum amount base from previous value on slicer power bi

Question

I have a table like this:

Year        Month       Code     Amount
---------------------------------------   
2017        11           a        7368    
2017        11           b        3542    
2017        12           a        4552    
2017        12           b        7541    
2018         1           a        6352    
2018         1           b        8376    
2018         2           a        1287    
2018         2           b        3625

I make slicer base on Year and Month (ignore the Code), and I want to show SUM of Amount like this :

If I select on slicer Year 2017 and Month 12, the value to be shown is SUM Amount base on 2017-11, and select on slicer Year 2018 and Month 1 should be SUM Amount base on 2017-12

I have tried this one for testing with, but this not allowed:

Last Month = CALCULATE(SUM(Table[Amount]); Table[Month] = SELECTEDVALUE(Table[Month]) - 1)

How to do it right?

I want something like this

NB: I use direct query to SQL Server

Update: At this far, I added Last_Amount column in SQL Server Table by sub-query, maybe you guys have a better way for my issue

You should also show us what the expected output looks like. The logic is not entirely clear to me, just based on your description. — Tim Biegeleisen
@TimBiegeleisen i can't upload picture yet, but maybe it will looks like this ibb.co/eGJTce — Adrin Pratama

Alexis Olson Alexis Olson · Accepted Answer · 2018-08-24T15:27:51

The filters in a CALCULATE statement are only designed to take simple statements that don't have further calculations involved. There are a couple of possible remedies.

1. Use a variable to compute the previous month number before you use it in the CALCULATE function.

Last Month = 
    VAR PrevMonth = SELECTEDVALUE(Table[Month]) - 1
    RETURN CALCULATE(SUM(Table[Amount]), Table[Month] = PrevMonth)

2. Use a FILTER() function. This is an iterator that allows more complex filtering.

Last Month = CALCULATE(SUM(Table[Amount]),
                 FILTER(ALL(Table),
                     Table[Month] = SELECTEDVALUE(Table[Month]) - 1))

Edit: Since you are using year and month, you need to have a special case for January.

Last Month = 
    VAR MonthFilter = MOD(SELECTEDVALUE(Table[Month]) - 2, 12) + 1
    VAR YearFilter = IF(PrevMonth = 12,
                         SELECTEDVALUE(Table[Year]) - 1,
                         SELECTEDVALUE(Table[Year]))
    RETURN CALCULATE(SUM(Table[Amount]),
               Table[Month] = MonthFilter,
               Table[Year] = YearFilter)

Sum amount base from previous value on slicer power bi

1 Answers