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?

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

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!

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

Happy New Year from Power BI

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