Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Monthly moving average

Hello,

 

I'm trying to compute a montly moving average of 3 months

So, this is what I tried:

Msr MovingAverageSheReview = 

    VAR NumMonths = 3
    VAR LastCurrentDate = MAX (fctTable[DateOfIncident])
    VAR Period = DATESINPERIOD(fctTable[DateOfIncident],LastCurrentDate, - NumMonths, MONTH)
    VAR MovingAverage = 
        CALCULATE(
            AVERAGEX(
                VALUES(DateTable[Year-Month]), fctTable[Msr AverageSheReview]
            ),
            Period
        )
    RETURN
        MovingAverage

With:

Msr AverageSheReview = 
    CALCULATE(
        AVERAGE(fctTable[#dagen SheReview]),
        fctTable[Status] = "Completed"
    )

Note that I'm trying to compute the 3month moving average  of a monthly average of column fctTable[#dagen SheReview]

 

With I plot Msr MovingAverageSheReview in a table with Month-Year of datetable, it show the monthly average, but not the moving average.

 

Can someone help me?

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    HI Anonymous,

    You can try to use the following measure formula to calculate the moving average based on the current date:

    Msr MovingAverageSheReview =
VAR NumMonths = 3
VAR CurrDate =
    MAX ( fctTable[DateOfIncident] )
VAR PrevDate =
    DATE ( YEAR ( currDate ), MONTH ( CurrDate ) - NumMonths, DAY ( CurrDate ) )
RETURN
    CALCULATE (
        [Msr AverageSheReview],
        FILTER (
            ALLSELECTED ( fctTable ),
            fctTable[DateOfIncident] >= PrevDate
                && [DateOfIncident] <= CurrDate
        )
    )

    Regards,

    Xiaoxin Sheng

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , try a measure like example

     

    Rolling 3 = calculate(AverageX(Values('Date'[MONTH Year]),CALCULATE(sum(Sales[Sales Amount]))),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      The following just yields the sum for each month. Not the moving average.

      Msr MovingAverageSheReview = 
CALCULATE(
    AVERAGEX(
        VALUES(DateTable[Year-Month]),
        CALCULATE(
            AVERAGE(fctTable[#dagen SheReview]),
            fctTable[status] = "Completed"
        )
    ),
    DATESINPERIOD(fctTable[DateOfIncident],MAX(fctTable[DateOfIncident]),-3,MONTH)
)

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    HI Anonymous,

    You can try to use the following measure formula to calculate the moving average based on the current date:

    Msr MovingAverageSheReview =
VAR NumMonths = 3
VAR CurrDate =
    MAX ( fctTable[DateOfIncident] )
VAR PrevDate =
    DATE ( YEAR ( currDate ), MONTH ( CurrDate ) - NumMonths, DAY ( CurrDate ) )
RETURN
    CALCULATE (
        [Msr AverageSheReview],
        FILTER (
            ALLSELECTED ( fctTable ),
            fctTable[DateOfIncident] >= PrevDate
                && [DateOfIncident] <= CurrDate
        )
    )

    Regards,

    Xiaoxin Sheng

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you very much, this works.

      For educational purposes:

      Why is the ALLSELECTED necessary here?

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        HI Anonymous,

        It means the keep current filter effects which applied to the table and change the expression calculation range to that table. (rolling calculation should be calculated across the current row contexts groups)

        ALLSELECTED function (DAX) - DAX | Microsoft Docs

        Regards,

        Xiaoxin Sheng