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 date...
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

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

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

        Hi v-qiuyu-msft

         

         

        I think my issue seems to be when theres blank months data, and i try take it out with a page report filter, and that causes problems. Otherwise it seems to work.