Rolling 3 week Average

Hi @cottrera ,

According to my understanding, The error was caused because the syntax was incorrect.
Syntax of RANKX and DATE function:

RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]]) 
DATE(<year>, <month>, <day>)

Optional arguments might be skipped by placing an empty comma (,) in the argument list. Try the following formula:

Week Rank = RANKX(ALL('SHARED Calendar'),'SHARED Calendar'[Week start],,ASC,DENSE)

Because the WEEKNUM function calculates the number of weeks in a year, to distinguish the number of weeks in different years, add columns to Facts Table:

Different Weeks = INT('Facts Table'[Repair Completion].[Year]-MIN('Facts Table'[Repair Completion].[Year])*(365/7))
Week Number = WEEKNUM('Facts Table'[Repair Completion]) + 'Facts Table'[Different Weeks]

Then create measure：

Last 3 weeks = CALCULATE(AVERAGE('Facts Table'[Repair Ref]), FILTER(ALL('Facts Table'),'Facts Table'[Week Number]>MAX('Facts Table'[Week Number])-3 && 'Facts Table'[Week Number]<=max('Facts Table'[Week Number])))

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,

Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

cottrera , You can use week rank for that

new columns in date table

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) //YYYYMM 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 3 weeks = CALCULATE(Avergae('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-8 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

or

Last 3 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-8 && 'Date'[Week Rank]<=max('Date'[Week Rank])))/CALCULATE(distinctcountd(Date[Week Rank]) , FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-8 && 'Date'[Week Rank]<=max('Date'[Week Rank]) && not(isbalnk(sum('order'[Qty])))))

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

Hi cottrera ,

If you are creating a calculated column, then the cause of the eeors is that you added two commas after "ASC". You should add two commas before "ASC" to conform to the syntax. Try the following formula:

Week Rank = 
RANKX( 
    ALL('SHARED Calendar'), 
    'SHARED Calendar'[Week start], 
    , 
    ASC, 
    DENSE 
) 

 If you want to create measure, follow these steps to create two measures:

Measure 3 = MAX('SHARED Calendar'[Week start]) 

Week Rank = 
RANKX( 
    ALL('SHARED Calendar'), 
    [Measure 3], 
     , 
     ASC, 
     Dense 
) 

 If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
 
Best Regards,
Winniz
 
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.