Forum Discussion
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
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
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.
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
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.
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.
Thank you both AlexisOlson & MattAllington both solutions have worked perfectly - thank you
