Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Oomsen
Helper III
Helper III

Cumulative sum of a measure

This is my measure Cashflow forecast = CALCULATE(SUM(Beginbalans[Periode beginsaldo]) + [Liquide middelen] + SUM('Debiteuren'[Bedrag]) + SUM('Crediteuren'[Bedrag])) I would like to have the cumulative per month. 

1 ACCEPTED SOLUTION

Ah... there is your problem. What you're trying to do is calculated a running total. 
Now in your example, there is a row context. So for every row you're doint the calculation. What you should do, is create the a total till the current rowcontext. 

A running total is explained here at the DAX patterns site. 

 

Query should look like this: 

MeasureName = 
CALCULATE (
    [YourMeasure];
    FILTER (
        ALL ( datetable[date] );
       datetable[date] <= MAX ( datetable[date] )
    )
)


I've also created an example file for you which you can download here. 


Hope this works for you. 
- Marc

View solution in original post

3 REPLIES 3

Why are you using Calculate in this case? You aren't applying any filter context. 

Regarding your question. Add this measure to a column chart with the months on your x-axis... This should work, in case this results in the same value for every month, check your relationships in your model and your DAX measures. Your referencing other measures like [Liquide midellen] which might impact the filter context of your measure. 

- Marc

I thougt Calculate wass neccesary. Now i see it doesn't. But the result of sum is the amount per month and not the cumulative per month. this is my result:

                Openingbalans      Cashchanges      Receivables     Payables   Cashflow

Jan           362441,14              -94613,17           0                     0                267827,97

Feb           0                            -271768,02         0                     0                -271768,02

 

What i want to see is 267827,97 - 271768.,02 = -3940,05

Ah... there is your problem. What you're trying to do is calculated a running total. 
Now in your example, there is a row context. So for every row you're doint the calculation. What you should do, is create the a total till the current rowcontext. 

A running total is explained here at the DAX patterns site. 

 

Query should look like this: 

MeasureName = 
CALCULATE (
    [YourMeasure];
    FILTER (
        ALL ( datetable[date] );
       datetable[date] <= MAX ( datetable[date] )
    )
)


I've also created an example file for you which you can download here. 


Hope this works for you. 
- Marc

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.