How to get 3 month Rolling Average from a measure

[sidvix917]

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

[amitchandak · Accepted solution]

@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

[sidvix917]

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