cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Liism Regular Visitor
Regular Visitor

Cumulative measure with opening balance

Hi, 

 

My data looks like this: 

cumul.PNG

 

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

4 REPLIES 4
Community Support Team
Community Support Team

Re: Cumulative measure with opening balance

HI @Liism ,

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Super User
Super User

Re: Cumulative measure with opening balance

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.

Liism Regular Visitor
Regular Visitor

Re: Cumulative measure with opening balance

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! 

Liism Regular Visitor
Regular Visitor

Re: Cumulative measure with opening balance

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). 

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 127 members 1,599 guests
Please welcome our newest community members: