Forum Discussion

amaddala's avatar
amaddala
Icon for Helper I rankHelper I
5 years ago

Moving average with custom calendar

I need help calculating moving average of last 4 fiscal weeks in my data. We cannot use datesinperiod as I need to use "Week" in my steps. I have fiscalweek number ( for ex: 31, 32, 33 etc )  Please...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

amaddala , this way week is sortable, hope you have W01.

Create a Rank on this , a new column

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

 

Try these measures - example measures

 

This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
Last 4 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-4 && 'Date'[Week Rank]<=max('Date'[Week Rank])))