Forum Discussion
Calculate three months
WillY_OneEyed , You can use a measure with help from the calendar table.
3 Month Avg = CALCULATE(AverageX(Values('Date'[MONTH Year]),calculate(Sum('Table'[Value])))
,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))
Window function
Rolling 3 Avg = 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))) )
New visual Calc
Move Avg = MOVINGAVERAGE([Net],3)
Visual Calculations in Power BI- February 2024 Update RUNNINGSUM, RANGE, MOVINGAVERAGE,COLLAPSE, COLLAPSEALL, EXPAND, EXPANDALL, FIRST, LAST, PREVIOUS, and NEXT
https://www.youtube.com/watch?v=bKD9T0EWgQo&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L
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
Hi amitchandak, Thank you for the answer. I tested your "3 Month Avg" solution but the problem happens when I've a year filter applied. This filter is the default for this dashboard and can't be removed.
About the "Rolling 3 Avg" solution, I couldn't get it running because it gives an error of "few arguments"
When I've a year or a month visual filter applied, this measure doesn't work correctly
3 Month Avg = CALCULATE ( AVERAGEX ( VALUES ( 'Calendar'[Key] ), CALCULATE ( SUM ( 'Table'[KPI] ) ) ), DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), -3, MONTH ) )