Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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.
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.
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
@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
Hi
Thanks for for fast response
I am getting this error though can you advise
Richard
User | Count |
---|---|
92 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |