cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rbartk01
Frequent Visitor

Cumulative sum with Date Hierarchy

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!date hierarchy.PNGdate.PNG

 

1 ACCEPTED SOLUTION
AnthonyTilley
Solution Sage
Solution Sage

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
AnthonyTilley
Solution Sage
Solution Sage

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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:

 

Cumulative sum for categories.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Thanks in advance!

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

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors