Forum Discussion

Bencreamer83's avatar
Bencreamer83
New Member
10 years ago
Solved

Rolling average by week number

Could someone help me with creating either a calculated column or measure to find a 4 week rolling average? I am using the week number and it seems as if the Dax calculations do not recognize the week number as an actual date. For example:
Week. Weekly avg: 4-week AVG:
21. 380
22. 420
23. 560
24. 310
25. 635
26. 570
  • Eric_Zhang's avatar
    Eric_Zhang
    10 years ago

    Bencreamer83

     

    One way to get the 4 weeks' rolling AVG. Sort by weekNo ascending and create an index column.

     

     

    Then create a calculated column as

     

    4 weeks' rolling avg = 
SUMX (
    FILTER (
        ALL ( Table4 ),
        Table4[Index] <= EARLIER ( Table4[Index] )
            && Table4[Index]
                > EARLIER ( Table4[Index] ) - 4
    ),
    Table4[sales]
)
    / COUNTX (
        FILTER (
            ALL ( Table4 ),
            Table4[Index] <= EARLIER ( Table4[Index] )
                && Table4[Index]
                    > EARLIER ( Table4[Index] ) - 4
        ),
        Table4[WeekNo]
    )

2 Replies

  • Eric_Zhang's avatar
    Eric_Zhang
    Icon for Microsoft Employee rankMicrosoft Employee
    10 years ago

    Bencreamer83

     

    One way to get the 4 weeks' rolling AVG. Sort by weekNo ascending and create an index column.

     

     

    Then create a calculated column as

     

    4 weeks' rolling avg = 
SUMX (
    FILTER (
        ALL ( Table4 ),
        Table4[Index] <= EARLIER ( Table4[Index] )
            && Table4[Index]
                > EARLIER ( Table4[Index] ) - 4
    ),
    Table4[sales]
)
    / COUNTX (
        FILTER (
            ALL ( Table4 ),
            Table4[Index] <= EARLIER ( Table4[Index] )
                && Table4[Index]
                    > EARLIER ( Table4[Index] ) - 4
        ),
        Table4[WeekNo]
    )