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

MAX value of a Rolling Average result set

I am stuck trying to build a measure to determine the best or max 7 day average over a period longer than 7 days.

The measure needs to calculate the average value for days 1-7 then days 2-8 then days 3-9 and then determine which of those results is the MAX 7 day AVERAGE. Could you help please?

7 REPLIES 7
Community Support
Community Support

Hi @JohnHS ,

 

You may create a calendar table using DAX below, then create relationship with your fact data table on date field.

 

Calendar= CALENDARAUTO()

 

Then you create measures like DAX below.

 

Rolling 7 day AVERAGE = CALCULATE (AVERAGE( Table1[Value] ),DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), -7, DAY ))



MAX 7 day AVERAGE= MAXX(Table1, [Rolling 7 day AVERAGE] )

 

Best Regards,

Amy 

 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you for your advice, I had a calendar table before I esked my 1st question. I have no problem computing the 7 day rolling average using: 

7 Day Avg  =
AVERAGEX(
DATESINPERIOD('Calendar'[DateKey],
LASTDATE('Calendar'[DateKey]), -7, DAY),
'DevDrill Measures'[DevDrill Actual])
 
I have tried several suggestions to find the MAX of the 7 day AVG but as you can see in the MAX colum the result is incorrect.
 
Annotation 2020-06-10 082411.jpg
Community Support
Community Support

Hi @JohnHS ,

 

You may create measure like DAX below to get Max of the 7 day AVG, then put it into Card visual instead of original table visual.

 

MAX 7 day AVERAGE= MAXX(Table1, [Rolling 7 day AVERAGE] )

 

Best Regards,

Amy 

 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi

Your solution produces an error because the [Rolling 7 day AVERAGE] is a measure.

Also I dont understand why MAXX would work here I dont want to iterate I want to find the MAX of [Rolling 7 day AVERAGE] which is an iterated function

 

This is driving me mad I feel like the solution should be very simple but there must be a key concept I am missing.

Community Support
Community Support

Hi @JohnHS ,

 

I know you need to get MAX of the 7 day AVG, and to get the max of a measure, the MAXX function is effective. I also know the [Rolling 7 day AVERAGE]  is a measure, and could be used in MAXX function. Note that the formula above is created in measure instead of calculated column, or could you please show us the error message for further analysis?

 

Best Regards,

Amy 

Super User IV
Super User IV

@JohnHS , Try with Date Calendar

Rolling 7 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-7,Day))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Hi, your solution returns a result but I cant quite understand what that result is. It is returning a value of 3.5  But when I apply my rolling average formula:

AVERAGEX(
DATESINPERIOD('Calendar'[DateKey], LASTDATE('Calendar'[DateKey]), -7, DAY),
'DevDrill Measures'[DevDrill Actual]))
I can see that the MAX value in the result set is 18.5 I just cant figure out how to compute that value 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors