cancel
Showing results for
Did you mean:

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

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
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

2 REPLIES 2
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

Helper I

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))

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors