Forum Discussion
Cumulative Sum with filter
I am trying to do a cumulative measure to add to a chart, however becuase my data structure consist of stacked monthly reports if I do so using the below code I end up with summing over repeated instances as each ID appears more than once, therefore I need to filter on the most recent report date - how would I adapt the code below to reflect this?
Cumulative Quantity :=
CALCULATE (
SUM ( Transactions[Quantity] ),
FILTER (
ALL ( 'Date'[Date] ),
'Date'[Date] <= MAX ( 'Date'[Date] )
)
)
Ohh now I understand! Try this! :-)
Cumulative Quantity := VAR _date = SELECTEDVALUE('Table'[Date] )
VAR _period = SELECTEDVALUE('Table'[Report Date]) Return CALCULATE ( SUM ( Transactions[Quantity] ), ALL ( 'Table' ), 'Table'[Date] <= _date,
'Table'[Report Date] = _period
)
7 Replies
Try this:
Cumulative Quantity := VAR Mdate = MAX ( 'Date'[Date] ) Return CALCULATE ( SUM ( Transactions[Quantity] ), FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= Mdate ) )Br,
JohannesThanks tex628
I'm not sure this will work as the stack reports have the same date line duplicated in each - I have a column [report date] that I want to filter to be the max. I have included some sample data below, where the aim is to get the cumulative for each month for the April Report.
Report Date Date Value Jan-19 01/01/2019 500 Jan-19 01/02/2019 900 Jan-19 01/03/2019 800 Jan-19 01/04/2019 200 Feb-19 01/01/2019 500 Feb-19 01/02/2019 900 Feb-19 01/03/2019 200 Feb-19 01/04/2019 200 Mar-19 01/01/2019 500 Mar-19 01/02/2019 900 Mar-19 01/03/2019 200 Mar-19 01/04/2019 400 Apr-19 01/01/2019 500 Apr-19 01/02/2019 900 Apr-19 01/03/2019 200 Apr-19 01/04/2019 200 Can you convert the report date column into a proper date column and use that instead?
Jan-19 to 01/01/19
Feb-19 to 01/02/19
Etc...