cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JohnHS
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?

11 REPLIES 11
sedwards7
Frequent Visitor

Hi JohnHS,

 

I have come across your post, both here and stackoverflow. I am in a very similar position. I am trying to find the Max value from a Rolling 7 day average. I too have found the solutions you were given havent worked either. 

Did you manage to figure it out?

 

@v-xicai When I use MAXX(Table1,[Rolling 7 Day Avg]), it gives me the max Value and not the max average. Screenshot below

 

sedwards7_0-1649153070539.png

 

The number I am actually looking for is 4.33:

sedwards7_1-1649153137938.png

The Rolling 7 Day formula is:

Rolling 7 Day Avg = CALCULATE(AVERAGE(Table1[Value]),
DATESINPERIOD(Table1[Date], LASTDATE(Table1[Date]), - 7, Day)).
 
Please please please help. Thank you

Hi, First I run a rolling average..

7day Avg =
         AVERAGEX(
                DATESINPERIOD( 'Calendar'[DateKey]LASTDATE('Calendar'[DateKey]), -7DAY),
                [Value Field])
 
Then in a separate meaasure find the best in period..
Best 7 =
         MAXX('Calendar',[7day Avg])
I still dont understand why this works but it does 

After some help form my work colleague, he found that I wasnt using the correct functions in my MAXX measure. He used VALUES, which gave me the required result:

 

Max 7 Day Avg = MAXX(VALUES(TABLE[Date]),[7 Day Avg])

 

Hi JOHNSYLK,

 

Thank you for replying. Unfortunately your suggestion doesnt work either. This is really driving me insane, it shouldnt be this difficult.

 

I have created a measure to calculate the 7 day rolling average (one of many different ways of doing this) and it does give the correct result in the table.

7 Day Avg = AVERAGEX(
DATESINPERIOD(SWORPS[Date], LASTDATE(SWORPS[Date]), -7, DAY),
CALCULATE(AVERAGE(SWORPS[Value])))
 
However, when I use the MAXX formula on this, it brings back the MAX normal value and not the new average value:
Max 7 Day Avg = MAXX(SWORPS,[7 Day Avg])
v-xicai
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.

JohnHS
Frequent Visitor

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
v-xicai
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.

JohnHS
Frequent Visitor

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.

v-xicai
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 

amitchandak
Super User
Super User

@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.



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau 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
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors