Reply
Newcolator
Helper II
Helper II
Partially syndicated - Outbound

Cumulating by two different variables

Hi! 

 

I am creating a cumulative total of value by day. Using this data.

 

Newcolator_0-1718632452943.png

 
I create a quick measure
 
Cumulative total by Day =
CALCULATE(
    SUM('Sheet1'[value]),
    FILTER(
        ALLSELECTED('Sheet1'[day]),
        ISONORAFTER('Sheet1'[day], MAX('Sheet1'[day]), DESC)
    )
)
 
And it works.
 
Newcolator_1-1718632565157.png

 

If I use the same DAX measure but show it by 3 day group it doesn't work. I assumed it would work because 3 day group is ordered the same as day. I thought cumulating by day would automatically work over 3 day group, but no.

 

Newcolator_2-1718632605140.png

 

I have to create a new DAX measure to calculate the running total by 3 day group.

 

Cumulative Total by 3 day group =
CALCULATE(
    SUM('Sheet1'[value]),
    FILTER(
        ALLSELECTED('Sheet1'[3 day group]),
        ISONORAFTER('Sheet1'[3 day group], MAX('Sheet1'[3 day group]), DESC)
    )
)
 
And that works.
 
Newcolator_3-1718632659669.png

 

Is it possible to do both of these with just one DAX expression? It would be useful not to have to duplicate all my dax measures when I want to cumulate over a different variable.

1 ACCEPTED SOLUTION
tharunkumarRTK
Super User
Super User

Syndicated - Outbound

@Newcolator 

You can follow this pattern:

Cumilative = CALCULATE(SUM('Table'[value]), FILTER(ALLSELECTED('Table'[day], 'Table'[3 day group]), 'Table'[day] <= MAX('Table'[day]) && 'Table'[3 day group] <= MAX('Table'[3 day group])) ) 

 



Need Power BI consultation, hire me on UpWork .


If the post helps please give a thumbs up



If it solves your issue, please accept it as the solution to help the other members find it more quickly.




Tharun



View solution in original post

2 REPLIES 2
tharunkumarRTK
Super User
Super User

Syndicated - Outbound

@Newcolator 

You can follow this pattern:

Cumilative = CALCULATE(SUM('Table'[value]), FILTER(ALLSELECTED('Table'[day], 'Table'[3 day group]), 'Table'[day] <= MAX('Table'[day]) && 'Table'[3 day group] <= MAX('Table'[3 day group])) ) 

 



Need Power BI consultation, hire me on UpWork .


If the post helps please give a thumbs up



If it solves your issue, please accept it as the solution to help the other members find it more quickly.




Tharun



Syndicated - Outbound

Wonderful, it works. And presumably I can add more variables to the measure using the same pattern?

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)