cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
rbartk01 Frequent Visitor
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

Accepted Solutions
AnthonyTilley Established Member
Established Member

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

 

View solution in original post

3 REPLIES 3
AnthonyTilley Established Member
Established Member

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

 

View solution in original post

rbartk01 Frequent Visitor
Frequent Visitor

Re: Cumulative sum with Date Hierarchy

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

rbartk01 Frequent Visitor
Frequent Visitor

Re: Cumulative sum with Date Hierarchy

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!

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 132 members 1,833 guests
Please welcome our newest community members: