Forum Discussion

YweiB's avatar
YweiB
Frequent Visitor
3 years ago

Need Help in Visualization for 13 week moving average

Hi all,   Currently I have a list of sales data from 1/7/2019 to 6/19/2023. I used window function to calculate the rolling 13 week average for this sales data. Here is the function of Rolling ave...
Need Help
Tips and Tricks
amitchandak's avatar
amitchandak
Super User
3 years ago

YweiB , Create a date table join with date and use week rank column

 

13 WK MA V2 =
calculate(
AVERAGEX(values('Date'[Week Rank])
,
'KPI List'[Total Sales]
) ,WINDOW(
-12,REL,0,REL,
ALLSELECTED('Date'[Week Rank]),
ORDERBY('Date'[Week Rank],asc)

))

 

 

Week Rank

 

Have these new columns in Date Table, Week Rank is Important in Date/Week Table

Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format

 

 

 

YweiB's avatar
YweiB
Frequent Visitor
3 years ago

Thank you Amitchandak.

 

I just created the Week Rank and use the window function to calculate the 13 weeks moving average.

 

Could you tell me how to viualize this moving average in line chart to compare the 2022 and 2023 with week rank? adding the filter 2023 will make the number of first 12 week off. how to make the number is cut by filters. I just want to display the number of moving average

 

Thanks,