Rolling Average With Non-continuous Week Number

[Anonymous]

Hi All,

I am trying to build a 4 week rolling average formula for my dataset that has a date table with fiscal week or fiscal week number. What I am trying to use is the fiscal week number (1-52). I have a rolling 4 week average formula that would works, but when the current week is either 1, 2, or 3, the formula would not take the rolling 4 weeks (ex. current week 1 should give average of week 1, 52 of LY, 51 of LY, and 50 of LY). Is there a way to modify the below formula or to have a new formula where week 1, 2, and 3 of every year would include weeks from the past year? Below is a very simple layout of the data and my current formula

4 Week moving AVG= CALCULATE(
VAR CurrentWeek= SELECTEDVALUE('Date'[fiscalweek_n])
VAR MaxWeekNum = CALCULATE(MAX('Date'[fiscalweek_n]), ALL('Date'))

RETURN

AVERAGEX(
FILTER(ALLSELECTED('Date'),
'Date'[fiscalweek_n] <= CurrentWeek
&& 'Date'[FiscalWeek_n] > CurrentWeek - 4
&& 'Date'[FiscalYear] = CurrentYear),
[Totalsales]))

This has been bothering me for so long. Any help would be appreciated! 

[Vera_33]

Hi @Anonymous 

Add a column using YYYYWeeknumber format, then modify your measure

yearweek =  'Date'[FiscalYear]*100+'Date'[fiscalweek_n]

4 Week moving AVG= CALCULATE(
VAR CurrentWeek= SELECTEDVALUE('Date'[yearweek])
RETURN
AVERAGEX(
TOPN(4,FILTER(ALL('Date'),'Date'[yearweek]<=CurWeek),[yearweek],DESC),[Totalsales])

[amitchandak]

@Anonymous , You can create a week rank on Week start date or week number

examples

new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

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])))

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8