Forum Discussion
Cumulative sum with Date Hierarchy
Hi rbartk01
you have to look at this as when using date on its own there is only one level to your matrix so the all selected gets all dates selected in that header in this case all your dates are included in the calculation
when using the hierachy you have 4 levels Year, Quater, Month and day so when drilled down to the day level the row is showing only days selected in that level whish in this case is the one day.
to correct this remove the [DATE] part of your all selected function
Cumulative sum = CALCULATE( SUM('Sanitized CopyData'[Net Benefits]), FILTER( ALLSELECTED('Sanitized CopyData'), 'Sanitized CopyData'[Date] <= MAX('Sanitized CopyData'[Date]) ) )
Solution SageHi rbartk01
you have to look at this as when using date on its own there is only one level to your matrix so the all selected gets all dates selected in that header in this case all your dates are included in the calculation
when using the hierachy you have 4 levels Year, Quater, Month and day so when drilled down to the day level the row is showing only days selected in that level whish in this case is the one day.
to correct this remove the [DATE] part of your all selected function
Cumulative sum =
CALCULATE(
SUM('Sanitized CopyData'[Net Benefits]),
FILTER(
ALLSELECTED('Sanitized CopyData'),
'Sanitized CopyData'[Date] <= MAX('Sanitized CopyData'[Date])
)
)
Dear AnthonyTilley ,
This worked! Thank you so much!
I felt that this is the case of calculating values of particular day after drilling down into day level of Date Hierarchy, but didn't know how to overcome this... and this was that easy to fix.
Once again thanks.
Best Regards,
Rafal
Dear AnthonyTilley,
Sorry for bothering, but do you have any idea how I could apply this measure of cumulative sum split by some other column of categories? Right now it set's cumulative value for particular date for all categories, what multiplies cumulative sum by number of categories in stacked barchart. See how it looks on pic:
Thanks in advance!
