Calculate three months

[WillY_OneEyed]

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 = MONTH(MAX('Calendar'[Date]))
    VAR _currentYear = YEAR(MAX('Calendar'[Date]))
    VAR _previous1Month = IF(_currnetMonth = 1, 12, _currnetMonth-1) //to kpi FDS
    VAR _previous2Month = IF(_currnetMonth = 2, 12, _currnetMonth-2) //to kpi FDS
    VAR _previous3Month = IF(_currnetMonth = 3, 12, _currnetMonth-3) //to kpi FDS
    VAR _previousYearFDS = IF(_currnetMonth = 1, _currentYear-1, _currentYear) //to kpi FDS
    VAR firstmonth = 
        CALCULATE(
            [Dynamic Measure Base R],
            ALL('Calendar'),
            MONTH('Calendar'[Date]) = _previous1Month,
            YEAR('Calendar'[Date]) = _previousYearFDS)
    VAR secondmonth = 
        CALCULATE(
            [Dynamic Measure Base R],
            ALL('Calendar'),
            MONTH('Calendar'[Date]) = _previous2Month,
            YEAR('Calendar'[Date]) = _previousYearFDS)
    VAR thirdmonth = 
        CALCULATE(
            [Dynamic Measure Base R],
            ALL('Calendar'),
            MONTH('Calendar'[Date]) = _previous3Month,
            YEAR('Calendar'[Date]) = _previousYearFDS)

RETURN 
(firstmonth + secondmonth + thirdmonth) / 3

Can anyone help me?

[amitchandak]

@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

[WillY_OneEyed]

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"

[WillY_OneEyed]

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