cancel
Showing results for
Did you mean:
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
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

The number I am actually looking for is 4.33:

The Rolling 7 Day formula is:

Rolling 7 Day Avg = CALCULATE(AVERAGE(Table1[Value]),
DATESINPERIOD(Table1[Date], LASTDATE(Table1[Date]), - 7, Day)).

New Member

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
Frequent Visitor

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])

Frequent Visitor

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])
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.

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.

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.

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.

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

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

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 !!
Frequent Visitor

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

Announcements