4 week moving average for specific product

[milkynight]

Hi all,

I would like to ask for your help/guidance on how to calculate 4w moving average for product codes/ product family. The idea is to have a Product code & Product family filters so the users can choose the product they would like to see the movement.

So far i can only build the 4WMA for 1 specific product code, and if i have total of 44 or more, i have to build 44+ times which is a manual process.

This is the link to get my data sample: https://drive.google.com/file/d/17G4tDpAsS0RtPI0-sAhVosqS2svfbF1E/view?usp=sharing 

I appreciate your help for a better way. Thanks much!

[milkynight]

Thanks @amitchandak , I tried again and it worked nicely. Appreciate your help!

Just want to check if we need to have Week Rank column? As the Week column has been in the right order i think.

[amitchandak · Accepted solution]

@milkynight , Create a new week year table with distinct year and week (Say Date)

Add columns

Year Week = [year]*100 +[week]

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

Try measures like

Last 4 weeks = CALCULATE(sum('Table'[sales]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-4 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

Last 4 weeks Average = CALCULATE(Average ('Table'[sales]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-4 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

Last 4 weeks Average = CALCULATE(sum('Table'[sales]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-4 && 'Date'[Week Rank]<=max('Date'[Week Rank])))/4