Forum Discussion

WillY_OneEyed's avatar
WillY_OneEyed
New Member
2 years ago

Calculate three months

Hi guys,  I need to calculate 3 months separately, sum and divide by 3 and I did this but something is going wrong:   _Measure = VAR _kpi = SELECTEDVALUE(KPIs[KPIName]) VAR _currnetMonth...
Need Help
amitchandak's avatar
amitchandak
Super User
2 years ago

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