Cumulative measure with opening balance

[Anonymous]

Hi, 

My data looks like this: 

Is there a way to create a measure for the cumulative sum (last column in the table) that takes into account also opening balance (before January in the table). So what I would need is to sum balance and cumulative balance from previous month. Also, that when I'm using slicer for years, then in 2019 it would take the Dec cumulative balance from 2018. 

So far I have this measure: 

Cumulative = calculate(sum(Data[AMOUNT]);filter(ALLSELECTED(Data); Data[Month]<=MAX(Data[Month])))

But this doesn't take opening balance into account and when I choose a new year, then it starts cumulating the balances from 0. 

Thank you in advance. 

Best regards,

Liis

[Ashish_Mathur]

Hi,

You show 2 Tables in your measure but your formula only refers to the Data Table.  Please share the actual data layout.  Also, the opening balance of 1500 should have some date against it.  So please assign some data to that figure.  Share data in a format that can be pasted in an Excel file.

[Anonymous]

I could add January 2018 date to the opening balance as it should be included in January's total. But the data is from the same table, just year is different (I wanted to show that when I use year slicer on date, then it would still calculate correct cumulative). 

[v-shex-msft]

HI @Anonymous ,

I'd like to suggest you to take a look at following blog about auto-exist filter if you are using same table date field as source of slicer.

Understanding DAX Auto-Exist

I think you need to use variable to store selected and apply all function on table to ignore filter effect:

Measure =
VAR selected =
    MAX ( Table1[Date] )
RETURN
    CALCULATE (
        SUM ( Table1[Amount] ),
        FILTER ( ALL ( Table1 ), [Date] <= selected )
    )

Regards,

Xiaoxin Sheng

[Anonymous]

This almost worked, but what is still tricky, is that I have an another slicer for different products and in case slicer is on, then it shows cumulative total for all the products (the above data was for one product only). 

Is there a way to add this as well in the measure? 

Thank you!