Rolling 3 week Average

[cottrera]

Hi 

Can you please help. I am trying to calculate a 3 week rolling average for the repairs completed by my company.

I have a facts table which has rows of repairs with a completion date. and I  have a calendar table with the relation made to the completed repairs date

So far I have one DAX measure 

Compl Repairs = COUNTROWS(Facts Table)

Calendar Table
DimDate	Year	Week Number
01/12/2020	2020	49
20/12/2020	2020	52
01/01/2021	2020	1
10/01/2021	2020	3
01/02/2021	2020	6
Facts Table
Repair Ref	Repair Completion  Date	Trade
12	01/12/2020	Plum
32	20/12/2020	Plum
545	01/01/2021	Plum
667	10/01/2021	Elec
876	01/02/2021	Elec

thank you

RIchard

[v-kkf-msft]

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.

[v-kkf-msft]

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]

Hi thank you for replying so quickly I am getting this error when trying to use the first of the function you provided

I have just had a conversation with a senior and the reporting requirement are now changing so I no longer need help on this issue.

thank you again

Richard

[amitchandak]

@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-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

[cottrera]

Hi 

Thanks for for fast response

I am getting this error though can you advise

Richard