Forum Discussion

Noki's avatar
Noki
Regular Visitor
4 years ago

Moving average - weekly

Hi! Data on receivables arrive on a specific day of the week. How can I create a variable weekly moving average? Thx
amitchandak's avatar
amitchandak
Super User
4 years ago

Noki , if you need across week

 

Have week rank in your date or week table, prefer a separate table

 

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

 

measure

Last 2 weeks = CALCULATE(AverageX(values('Date'[Week Rank]), calculate(sum('Table'[Qty]))) , FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-3 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

 

 

If you data at date level then  , Avg Above day level

 

AverageX(values('Date'[Date]), calculate(sum('Table'[Qty])))