Cumulating by two different variables

[Newcolator]

Hi! 

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

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.

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.

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.

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.

[tharunkumarRTK · Accepted solution]

@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

[Newcolator]

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