How to Create Dax Column that calculates a 4 week moving average?

[Anonymous]

Column A is a String,

Column B is the sum for each week.

Looking for help in getting column C which will be a 4 week moving average in DAX.

In the excel example below, cell C5 = Average (B5,B4,B3,B2), cell C6 =AVERAGE(B6,B5,B4,B3) & so on

 

A solution to this will be greatly appreciated.

[Anonymous · Accepted solution]

Hi @Anonymous ,

I suggest you to create a calculated table to help your calculation.

DimDate = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2023, 01, 01 ), DATE ( 2023, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "WeekNum", WEEKNUM ( [Date], 2 ),
    "WeekDay", WEEKDAY ( [Date], 2 )
)

Measure:

4 Week Moving Average = 
CALCULATE (
    AVERAGE ( 'Table'[Total] ),
    FILTER (
        ALLEXCEPT ( DimDate, DimDate[Year] ),
        MAX ( DimDate[WeekNum] ) - 4 >= 0
            && DimDate[WeekNum]
                > MAX ( DimDate[WeekNum] ) - 4
            && DimDate[WeekNum] <= MAX ( DimDate[WeekNum] )
    )
)

Result:

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.