cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bhmiller89
Helper V
Helper V

Rolling 90 Day Avg

I've combed the forums and have tried to replicate what others have done but with no luck. I need to show actual dollars won over a rolling 90 day average.

 

I wrote a measure to show Today's date but then got lost after that.  I have a column in my data called "Total Services Won$" and I need to total that over 90 days

 

I tried

Rolling90Day = CALCULATE([TotalServices$], 'dpmgr vwJMWebSalesOpportunitiesProduction'[CloseDate] >= TODAY()-90)

 

But I'm not sure if that's right

4 REPLIES 4
user_unknown39
Helper I
Helper I

Hi @v-huizhn-msft @bhmiller89 !

How would we compare this with the same period last year?

meaning rolling 90 based on the slider date versus the same 90 days of last year?

 

Thank-you.

 

v-huizhn-msft
Microsoft
Microsoft

Hi @bhmiller89,

As I tested, the syntax of the measure is right, and get the expected result. This measure will calculate the Total services since the last 90 days. But for your requirement, the TODAY() function is inappropriate. I try to reproduce your scenario, and get expected result as followings.

The first screenshot is the sample data.

1.png

 

Then I create a measure to calculate the rolling 90 day average and get the result shown in second snapshot.

90days_average = DIVIDE(CALCULATE(SUM(Test[Value]),FILTER(ALL(Test),AND(Test[Date]<=MAX(Test[Date]),Test[Date]>(MAX(Test[Date])-90)))),90)

 
2.png

For instance, for 2016/3/31, the measure will calculate the average during 2016/1/2-2016/3/31, for 2016/4/1, the measure will calculate the average during 2016/1/3-2016/4/1.

If you have any other issue, please feel free to ask.

Best Regards,
Angelia

Hi @v-huizhn-msft,

 

This makes sense. However, I don't need the ability to look at rolling totals on historic dates, only in real time. So if I look at the Dashboard this morning with refreshed data, I would see a rolling total 90 days before today, and then when I check tomorrow it will be 90 days from tomorrow. 

 

I'm really bad at using "date" tables and was trying to avoid that if possible! 

Hi @bhmiller89,

You'd better create a slicer to filter the date, please see the following screenshot, when you select the 2016/5/1, it will return the sum total of last 90 days from 2016/5/1.

Capture1.PNG

Best Regards,
Angelia

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.