Forum Discussion
Incremental into cumulative
Hi, Anonymous
According to your description, I can roughly understand your requirement, you want to sum the "amount" if it's the same product and Lob for all valuation date anterior to the one in the line and create a slicer to filter the data based on the date column, right? I think you can create a measure to use the AllSelected() function instead of All() like this to achieve your requirement:
Cumulative Amount 1 = CALCULATE(SUM('Table'[Amount]),FILTER(ALLSELECTED('Table'),[LoB]=MAX('Table'[LoB])&&[Valuation Date]<=MAX('Table'[Valuation Date])))And you can get what you want, like this:
You can download my test pbix file below
For more info about the Allselected() function in DAX, please refer to this link:
https://docs.microsoft.com/en-us/dax/allselected-function-dax
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community ChampionAnonymous you can use the following measure
Measure =
CALCULATE (
SUM ( 'Table'[Amount] ),
FILTER (
ALL ( 'Table' ),
'Table'[Valuation Date] <= MAX ( 'Table'[Valuation Date] )
),
KEEPFILTERS ( SUMMARIZE ( 'Table', 'Table'[Product], 'Table'[LoB] ) )
)
If you need calculated column
cumulativeSum =
SUMX (
FILTER (
'Table',
'Table'[Product] = EARLIER ( 'Table'[Product] )
&& 'Table'[LoB] = EARLIER ( 'Table'[LoB] )
&& 'Table'[Valuation Date] <= EARLIER ( 'Table'[Valuation Date] )
),
'Table'[Amount]
)