Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
sidvix917
Helper I
Helper I

How to get 3 month Rolling Average from a measure

Hello,

 

I'm attempting to obtain a three-month rolling average from a measure that was created to fill in missing values in various months. The current rolling average measure I developed works for the values in the table that were there but not for the values that were filled. Can somebody offer their knowledge on this? A link to the file is provided below.

 

Thanks.

 

https://we.tl/t-DmeNHCjazS

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@sidvix917 , You can try measure like examples 

 

3 Month Avg = CALCULATE(AverageX(Values('Date'[MONTH Year]),calculate(Sum('Table'[Value])))
,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))

 

Rolling 3 = CALCULATE(AverageX(Values('Date'[MONTH Year]),calculate(Sum('Table'[Value]))), WINDOW(-2,REL, 0, REL, ADDCOLUMNS(ALLSELECTED('Date'[Month Year],'Date'[Month Year Sort] ),ORDERBY([Month Year Sort],asc)))

 

 

Rolling Months Formula: https://youtu.be/GS5O4G81fww

Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@sidvix917 , You can try measure like examples 

 

3 Month Avg = CALCULATE(AverageX(Values('Date'[MONTH Year]),calculate(Sum('Table'[Value])))
,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))

 

Rolling 3 = CALCULATE(AverageX(Values('Date'[MONTH Year]),calculate(Sum('Table'[Value]))), WINDOW(-2,REL, 0, REL, ADDCOLUMNS(ALLSELECTED('Date'[Month Year],'Date'[Month Year Sort] ),ORDERBY([Month Year Sort],asc)))

 

 

Rolling Months Formula: https://youtu.be/GS5O4G81fww

Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f

Hey Amit,

 

Thank you very much. I used the following measure, and it works; I need to fine-tune it. I need to start the rolling average after the first two months and conclude with the last figure.

3 months rolling =
CALCULATE(AVERAGEX(VALUES(Dates[Month & Year]),[Last Date with Data]),DATESINPERIOD(Dates[Date],MAX(Dates[Date]),-3,MONTH))
sidvix917_0-1698120439466.png

 

 

sidvix917_1-1698120472135.png

 




Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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