Calculate weekly moving average

[Anonymous]

Hi,

My data looks like the below:

Project Count       |       YearWeek

15                                 201803

20                                 201802

30                                 201801

10                                 201753

40                                 201752

I have created the below measure to calculate the 12 week moving average from current date going back in time. However, I don't know how to move back in time across years, for example, for the year weeks 201803 or 201802 or 201801 I will need to include the project count for 201753, 201752 etc as the range is 12 weeks. I don't know how to add the logic: IF extracting a project count from the "01" of a year, the next project count should be added from CurrentYear -1 and the MAX week within that year....

12wk Average = 
CALCULATE(SUM(Table1[CountProj]),
    FILTER(
        ALL(Table1[YearWeek] ),
        (
            (
                Table1[YearWeek]
                    >= MAX (Table1[YearWeek] ) - 11
            )
                && (
                    Table1[YearWeek]
                        <= MAX ( Table1[YearWeek] ) - 0
                )
        )
    ))/12

Any help appreciated.

[Stachu · Accepted solution]

I'd use TOPN, e.g. something like this

12wk Average =
VAR LastWeek =
    MAX ( Table1[YearWeek] )
VAR Last12Weeks =
    TOPN (
        12,
        FILTER ( ALL ( Table1[YearWeek] ), Table1[YearWeek] <= LastWeek ),
        [YearWeek], DESC
    )
RETURN
    DIVIDE ( CALCULATE ( SUM ( Table1[CountProj] ), Last12Weeks ), 12 )