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.