topic Re: Cumulative sum for a summarize variable in DAX in DAX Commands and Tips

Cumulative sum for a summarize variable in DAX

Anonymous — Mon, 17 Jun 2024 15:57:49 GMT

Hello,

I have a problem

in my original table i had a measure that cumulates value in time and where filters works, it's :

CALCULATE(
SUM(table[Value]),

ALL(Date table),

FILTER(ALLSELECTED(Table[Date],Table[Date] <= DATE TABLE[Date]))

I have another table where the summarize results in the roiginal table
VAR _t1 = SUMMARIZE(new table,table[ID] (where id is Value + Key + Date),"Date",MAX(New table[Date]) etc ..;

Difference between Old table and new table is that the new table is the old table but with duplicates

I try to get back my cumulatives values but i don't know how to do this

I tried

CALCULATE(SUMX(_t1,[Value]),ALL(date table),FILTER(_t1,[Date]<=MAX(date table[date]))

But it don't work because it don't cumulates my value and don't have value that i had before

Do you know how to fix this problem ?

Thnak you in advance

Re: Cumulative sum for a summarize variable in DAX

Greg_Deckler — Mon, 17 Jun 2024 16:43:22 GMT

Anonymous No need for CALCULATE, just use a SUMX and your FILTER.

Re: Cumulative sum for a summarize variable in DAX

Anonymous — Tue, 18 Jun 2024 02:12:37 GMT

Hi Anonymous ,

Try the following expression:

Cumulative Value = VAR CurrentDate = MAX('Date Table'[Date]) VAR FilteredTable = FILTER( ALL('Date Table'), 'Date Table'[Date] <= CurrentDate ) RETURN SUMX( FILTER( _t1, [Date] IN VALUES(FilteredTable[Date]) ), [Value] )

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Re: Cumulative sum for a summarize variable in DAX

Anonymous — Tue, 18 Jun 2024 08:20:31 GMT

Hello

It don't work on the step : VALUES(FilteredTable[Date]) because he don't want to recognize FilteredTable ...

Re: Cumulative sum for a summarize variable in DAX

Anonymous — Tue, 18 Jun 2024 08:52:36 GMT

Hi Anonymous ,

Not quite sure what you mean, what is the error message it reports?

Best regards,
Community Support Team_ Scott Chang