Rolling average by week number

[Bencreamer83]

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 · Accepted solution]

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

[Bencreamer83]

Thank you!