Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Zarlot531
Helper V
Helper V

Having trouble getting a rolling sum to work properly

I have a dataset that provides "Starting RMR" (RMR is recurring monthly revenue) by year but then also shows the first cancellation by year. So in the pivot below I have RMR by start year going across the top and then by cancel year going down the left hand column. 

 

As you can see, I have  measure that brings back all of the started RMR by year and then also a measure that calculates the cancelled as of the year it cancelled. So I can easily show the % cancelled in any given year of a given start Year. For example, below you can see we added $244,231 in 2012 and $16,310 of this cancelled in 2017. 

 

What I want to do is have this as a *Rolling* sum. What I want to show where $16,310 is shown instead is $16,310 plus every number before that (12,467  ,  27,808, etc. etc. etc.). 

 

I have a date table... I just can't quite get it to work. Any ideas? 

 

Thanks, 

 

 

 

Rolling1.JPG

 

Rolling2.JPG

1 ACCEPTED SOLUTION

 

In the Dax formula below/second image below you'll see *a* solution. Not sure if it's the best one. But there's another weird problem. The Pivot Table doesn't sum the row grand totals of the rolling measure properly, even though everything else is summed and rolls properly within the columns. See first image below. The row grand total takes only the latest in the row into of summing it. Hmm. 

 

=calculate(sum([First Cancel RMR]),filter(allselected('Range'),Range[First Cancel Year]<=max([First Cancel Year]) && max(Range[StartYear]) = 'Range'[StartYear]))

 

 

Capture12.JPGCapture67.JPG

View solution in original post

5 REPLIES 5
PaulDBrown
Community Champion
Community Champion

Please provide sample data in a tabular structure (not an image) or sample PBIX file & a depiction of the expected outcome (from Excel for example)





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






I think I'm getting close with this... it's rolling, just rolling too much. need to add some more filter. Capture6666.JPG

 

Attached is a link to a file with more test data. You can see what I'm after on the summary tab. I really just need a rolling figure. I can get it to work except for the rolling % piece. 

 

Attrition File  

This actually works (the blue highlighted) but I need to get it to work when different industries are selected.. 

 

Zarlot531_0-1633052014121.png

 

 

In the Dax formula below/second image below you'll see *a* solution. Not sure if it's the best one. But there's another weird problem. The Pivot Table doesn't sum the row grand totals of the rolling measure properly, even though everything else is summed and rolls properly within the columns. See first image below. The row grand total takes only the latest in the row into of summing it. Hmm. 

 

=calculate(sum([First Cancel RMR]),filter(allselected('Range'),Range[First Cancel Year]<=max([First Cancel Year]) && max(Range[StartYear]) = 'Range'[StartYear]))

 

 

Capture12.JPGCapture67.JPG

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.