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

Need Help

3 Replies

  • 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

    • WillY_OneEyed's avatar
      WillY_OneEyed
      New Member
      2 years ago

      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's avatar
        WillY_OneEyed
        New Member
        2 years ago

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