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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Anonymous
Not applicable

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 
Anonymous
Not applicable

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

 

Anonymous
Not applicable

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.

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.

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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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