topic Re: Cumulating by two different variables in DAX Commands and Tips

Cumulating by two different variables

Newcolator — Mon, 17 Jun 2024 14:02:48 GMT

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.

Re: Cumulating by two different variables

tharunkumarRTK — Mon, 17 Jun 2024 16:03:16 GMT

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

Re: Cumulating by two different variables

Newcolator — Tue, 18 Jun 2024 08:07:38 GMT

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