Cumulative sum for a summarize variable in DAX

[francisthe63]

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

[Anonymous]

Hi @francisthe63 ,

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.

[francisthe63]

Hello

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

[Anonymous]

Hi @francisthe63 ,

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

Best regards,
Community Support Team_ Scott Chang

[Greg_Deckler]

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