cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

## Better Rolling Average Formula

Hi,

Wondering if anyone can help me improve my rolling average formula - I've got data that runs from 1 Jan 2020 to 31 July 2020 & I've created a 6 week rolling average using the below formula

Revs R42DA = DIVIDE(CALCULATE (
[Revenue],
DATESINPERIOD ( Dates[Date], LASTDATE ( Dates[Date] ), -42, DAY )),42)

However, the issue that this formula creates is for the 1st 6 weeks it's underreporting the rolling average

Is there a better formula I can use so the first 6 weeks are not being underreported - so ideally the first week is just a rolling average of that week and the second is for the first 2 weeks - and so on. Thanks in advance

Laurence

2 ACCEPTED SOLUTIONS

Your formula is great, but you have hard coded 42 days. But you don't have 42 days when the time period is in the first 41 days of data. Write a test measure that counts the days, eg countrows(DATESINPERIOD ( Dates[Date], LASTDATE ( Dates[Date] ), -42, DAY ))

and add it to the visual. I hope it will tell you how many days you are tracking.   Then divide by this instead of 42.

* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Super User

Another approach would be to use AVERAGEX, which should keep the numerator and denominator in sync.

``````Revs R42DA =
AVERAGEX (
DATESINPERIOD ( Dates[Date], LASTDATE ( Dates[Date] ), -42, DAY ),
[Revenue]
)``````

This is simpler but less computationally efficient than methods that don't require calculating Revenue for each day in the period separately.

3 REPLIES 3

Thank you both @AlexisOlson & @MattAllington  both solutions have worked perfectly - thank you

Super User

Another approach would be to use AVERAGEX, which should keep the numerator and denominator in sync.

``````Revs R42DA =
AVERAGEX (
DATESINPERIOD ( Dates[Date], LASTDATE ( Dates[Date] ), -42, DAY ),
[Revenue]
)``````

This is simpler but less computationally efficient than methods that don't require calculating Revenue for each day in the period separately.

Your formula is great, but you have hard coded 42 days. But you don't have 42 days when the time period is in the first 41 days of data. Write a test measure that counts the days, eg countrows(DATESINPERIOD ( Dates[Date], LASTDATE ( Dates[Date] ), -42, DAY ))

and add it to the visual. I hope it will tell you how many days you are tracking.   Then divide by this instead of 42.

* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.