3 month moving average as calculated column

[spoony]

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

[v-qiuyu-msft · Accepted solution]

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]

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

[v-qiuyu-msft]

Hi @spoony,

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

Best Regards,
Qiuyun Yu

[spoony]

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.