Skip to main content
cancel
Showing results for 
Search instead 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

Reply
LaurenceSD
Advocate II
Advocate II

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
LaurenceSD_0-1616009425310.png

 

 
 

 

 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.

View solution in original post

AlexisOlson
Super User
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.

 

View solution in original post

3 REPLIES 3
LaurenceSD
Advocate II
Advocate II

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

AlexisOlson
Super User
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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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