Better Rolling Average Formula

[LaurenceSD]

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

[MattAllington · Accepted solution]

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. 

[LaurenceSD]

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

[AlexisOlson · Accepted solution]

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.

[MattAllington · Accepted solution]

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.