Forum Discussion

spoony's avatar
spoony
Icon for Helper I rankHelper I
9 years ago
Solved

3 month moving average as calculated column

Hi, I know you can create a moving average as a measure below, but i need to create a moving average as a calculated column. How do you rewrite the below as a calculated column. I think it needs datesbetween.

 

Moving_Average_3_Months = 
CALCULATE (
    AVERAGEX ( 'Session', 'Session'[Sessions] ),
    DATESINPERIOD (
        'Session'[FullDate],
        LASTDATE ( 'Session'[FullDate] ),
        -3,
        MONTH
    )
)

 

Need Help
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    Hi spoony,

     

    You can create a calculated column like below:

     

    Moving_Average_3_Month = CALCULATE (
    AVERAGEX ( ALLSELECTED('Session'), 'Session'[Sessions] ),
    DATESINPERIOD (
        'Session'[FullDate],
        LASTDATE ( 'Session'[FullDate] ),
        -3,
        MONTH
    )
)

     

     

    Best Regards,
    Qiuyun Yu

4 Replies

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Icon for Community Support rankCommunity Support
    9 years ago

    Hi spoony,

     

    You can create a calculated column like below:

     

    Moving_Average_3_Month = CALCULATE (
    AVERAGEX ( ALLSELECTED('Session'), 'Session'[Sessions] ),
    DATESINPERIOD (
        'Session'[FullDate],
        LASTDATE ( 'Session'[FullDate] ),
        -3,
        MONTH
    )
)

     

     

    Best Regards,
    Qiuyun Yu

    • spoony's avatar
      spoony
      Icon for Helper I rankHelper I
      9 years ago

      Thanks i'll try use it. Seems to be having issues when theres other columns and having multiple same months.

      • v-qiuyu-msft's avatar
        v-qiuyu-msft
        Icon for Community Support rankCommunity Support
        9 years ago

        Hi spoony,

         

        If my DAX doesn't work, please share your sample data and expected results for our analysis.

         

        Best Regards,
        Qiuyun Yu