Forum Discussion
Calculate weekly moving average
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.
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 )
1 Reply
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 )