Forum Discussion
Cumulative Sum with filter
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
)
Thanks 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...
Yes it is in the date format already.
Relationship is between 'Table'[Date] and 'Date'[Date] and has to stay that way?
I'm not sure I follow? I don't think the relationship matters.
Below is an example of where I hope to get to using the above data, the cumulative for the latest report (Apr-19), as a measure not column.Report Date Date Value Cumulative Apr-19 01/01/2019 500 500 Apr-19 01/02/2019 900 1400 Apr-19 01/03/2019 200 1600 Apr-19 01/04/2019 200 1800