Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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

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.

General Comment
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.