Forum Discussion
De-cumulative values by date
Hello,
I have a data set that contains a cumulative column (it is connected to the BI service so I am trying to avoid using a calculated column, ideally I need a DAX measure).
I need to produce a line graph that always starts at zero and goes up using this cumulative column.
So essentially minus the earliest date selected from the whole column (when sliced) to start the line at zero and climb upwards.
Ive had an attempt at getting the EARLIEST date and minus it from the data but cant seem to get it to work...
Any ideas would be appreciated.
Thanks,
Example:
| 01/01/2017 | 10 |
| 01/02/2017 | 15 |
| 01/03/2017 | 20 |
| 01/04/2017 | 25 |
| 01/05/2018 | 30 |
| 01/06/2017 | 35 |
So the when sliced by 01/03/2017 onwards the line would start at 0 and then show 5, 10, 15.
HI Anonymous
Try this MEASURE
Measure = VAR EarliestDate = CALCULATE ( MIN ( TableName[Date] ), ALLSELECTED () ) VAR StartValue = CALCULATE ( SUM ( TableName[Cumulative Values] ), TableName[Date] = EarliestDate ) RETURN SUM ( TableName[Cumulative Values] ) - StartValue
3 Replies
- Zubair_Muhammad
Community Champion
HI Anonymous
Try this MEASURE
Measure = VAR EarliestDate = CALCULATE ( MIN ( TableName[Date] ), ALLSELECTED () ) VAR StartValue = CALCULATE ( SUM ( TableName[Cumulative Values] ), TableName[Date] = EarliestDate ) RETURN SUM ( TableName[Cumulative Values] ) - StartValue- Zubair_Muhammad
Community Champion
Anonymous
- AnonymousNot applicable
Worked perfectly. Thank you!