Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculate weekly moving average

Hi,   My data looks like the below:   Project Count       |       YearWeek 15                                 201803 20                                 201802 30                               ...
Need Help
Tips and Tricks
  • Stachu's avatar
    Stachu
    7 years ago

    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 )
Stachu's avatar
Stachu
Community Champion
7 years ago

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 )