cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Cumulative Sum Using a String Date Format on ALL Function

Hello Guys,

So, I have a measure that does a cumulative sum, it is all good if I put this on the "datekey" level.
Since I will never see it on a date level, I want to move it to a year-month level so the calculation can run faster.

CumSum:=CALCULATE([Sales],FILTER(ALL(DimDate[YearMonthNumber]),DimDate[YearMonthNumber]<=MAX(DimDate[YearMonthNumber])))

YearMonthNumber = 201708

This works just fine using the YearMonthNumber as Axis, but if I would like to use this formula to be able to provide the same results if the axis is YearMonth

YearMonth = 2017-Ago

If I substitute from YearMonthNumber to YearMonth on the formula it wont work since its not numerical.
Any work around on this ? Hierarchys ?  

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Hi @Anonymous,

 

Can you share some sample data to analysis?

 

In addition, you can try to use below formula to transfer the string date to number date, then use it as the filter parameter.

CumSum:=
var selectitem=CALCULATE(MAX('DimDate'[YearMonthNumber]),ALLSELECTED('DimDate'[YearMonth]))
return
CALCULATE([Sales],FILTER(ALL(DimDate),DimDate[YearMonthNumber]<=selectitem))

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

1 REPLY 1
Community Support
Community Support

Hi @Anonymous,

 

Can you share some sample data to analysis?

 

In addition, you can try to use below formula to transfer the string date to number date, then use it as the filter parameter.

CumSum:=
var selectitem=CALCULATE(MAX('DimDate'[YearMonthNumber]),ALLSELECTED('DimDate'[YearMonth]))
return
CALCULATE([Sales],FILTER(ALL(DimDate),DimDate[YearMonthNumber]<=selectitem))

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors