Cumulative sum with Date Hierarchy

[rbartk01]

Hi,

I try to create a measure for cumulative sum using below DAX formula:

Cumulative sum = 
CALCULATE(
	SUM('Sanitized CopyData'[Net Benefits]);
	FILTER(
		ALLSELECTED('Sanitized CopyData'[Date]);
		'Sanitized CopyData'[Date] <= MAX('Sanitized CopyData'[Date])
    )
)

What is interesting, this measure works perfectly but only if I am using Date column as Date. Whenever I switch to Date hierarchy - measure shows exactly the same values for both: sum of my [Net Benefits] column and measure Cumulative sum. I attach screenshots of both situations.

Do you have any idea how I could create measure of cumulative sum working with date hierarchies?

Thanks!

[AnthonyTilley · Accepted solution]

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])
    )
)

[rbartk01]

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!

[rbartk01]

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