Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
Can someone please help writing DAX RANK() / RANKX() using Year and Weeknumber column. Last column "WeekNumber" is the desired column through that function.
TimeID | Date | Year | Month | MonthName | Week | IsWorkingDay | WeekRange | WeeNumber |
20200301 | 01 March 2020 | 2020 | 3 | March | 10 | 0 | 01 Mar 20 - 07 Mar 20 | Week 1 |
20200302 | 02 March 2020 | 2020 | 3 | March | 10 | 1 | 01 Mar 20 - 07 Mar 20 | Week 1 |
20200303 | 03 March 2020 | 2020 | 3 | March | 10 | 1 | 01 Mar 20 - 07 Mar 20 | Week 1 |
20200304 | 04 March 2020 | 2020 | 3 | March | 10 | 1 | 01 Mar 20 - 07 Mar 20 | Week 1 |
20200305 | 05 March 2020 | 2020 | 3 | March | 10 | 1 | 01 Mar 20 - 07 Mar 20 | Week 1 |
20200306 | 06 March 2020 | 2020 | 3 | March | 10 | 1 | 01 Mar 20 - 07 Mar 20 | Week 1 |
20200307 | 07 March 2020 | 2020 | 3 | March | 10 | 0 | 01 Mar 20 - 07 Mar 20 | Week 1 |
20200308 | 08 March 2020 | 2020 | 3 | March | 11 | 0 | 08 Mar 20 - 14 Mar 20 | Week 2 |
20200309 | 09 March 2020 | 2020 | 3 | March | 11 | 1 | 08 Mar 20 - 14 Mar 20 | Week 2 |
20200310 | 10 March 2020 | 2020 | 3 | March | 11 | 1 | 08 Mar 20 - 14 Mar 20 | Week 2 |
20200311 | 11 March 2020 | 2020 | 3 | March | 11 | 1 | 08 Mar 20 - 14 Mar 20 | Week 2 |
20200312 | 12 March 2020 | 2020 | 3 | March | 11 | 1 | 08 Mar 20 - 14 Mar 20 | Week 2 |
20200313 | 13 March 2020 | 2020 | 3 | March | 11 | 1 | 08 Mar 20 - 14 Mar 20 | Week 2 |
20200314 | 14 March 2020 | 2020 | 3 | March | 11 | 0 | 08 Mar 20 - 14 Mar 20 | Week 2 |
20200315 | 15 March 2020 | 2020 | 3 | March | 12 | 0 | 15 Mar 20 - 21 Mar 20 | Week 3 |
20200316 | 16 March 2020 | 2020 | 3 | March | 12 | 1 | 15 Mar 20 - 21 Mar 20 | Week 3 |
20200317 | 17 March 2020 | 2020 | 3 | March | 12 | 1 | 15 Mar 20 - 21 Mar 20 | Week 3 |
20200318 | 18 March 2020 | 2020 | 3 | March | 12 | 1 | 15 Mar 20 - 21 Mar 20 | Week 3 |
20200319 | 19 March 2020 | 2020 | 3 | March | 12 | 1 | 15 Mar 20 - 21 Mar 20 | Week 3 |
20200320 | 20 March 2020 | 2020 | 3 | March | 12 | 1 | 15 Mar 20 - 21 Mar 20 | Week 3 |
20200321 | 21 March 2020 | 2020 | 3 | March | 12 | 0 | 15 Mar 20 - 21 Mar 20 | Week 3 |
Many thanks
Solved! Go to Solution.
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi,
I have removed unnecessry columns and wanted to calculate last column through DAX RANK using Year and Week column.
Date | Year | Month | Week | WeeNumber |
01 March 2020 | 2020 | 3 | 10 | Week 1 |
02 March 2020 | 2020 | 3 | 10 | Week 1 |
03 March 2020 | 2020 | 3 | 10 | Week 1 |
04 March 2020 | 2020 | 3 | 10 | Week 1 |
05 March 2020 | 2020 | 3 | 10 | Week 1 |
06 March 2020 | 2020 | 3 | 10 | Week 1 |
07 March 2020 | 2020 | 3 | 10 | Week 1 |
08 March 2020 | 2020 | 3 | 11 | Week 2 |
09 March 2020 | 2020 | 3 | 11 | Week 2 |
10 March 2020 | 2020 | 3 | 11 | Week 2 |
11 March 2020 | 2020 | 3 | 11 | Week 2 |
12 March 2020 | 2020 | 3 | 11 | Week 2 |
13 March 2020 | 2020 | 3 | 11 | Week 2 |
14 March 2020 | 2020 | 3 | 11 | Week 2 |
15 March 2020 | 2020 | 3 | 12 | Week 3 |
16 March 2020 | 2020 | 3 | 12 | Week 3 |
17 March 2020 | 2020 | 3 | 12 | Week 3 |
18 March 2020 | 2020 | 3 | 12 | Week 3 |
19 March 2020 | 2020 | 3 | 12 | Week 3 |
20 March 2020 | 2020 | 3 | 12 | Week 3 |
21 March 2020 | 2020 | 3 | 12 | Week 3 |
Hi @Anonymous ,
Create a Calculated Column
Thanks @harshnathani
It partially works.
I have date table which contains dates starting from year 2015 till 2040. I just copied a three weeks data.
Reason I wanted to Rank by [Year] and [Week] because every year ranking should start from 1.
Is it possible to copy data into PBIX file and attach back here. I wish I could attach a full file.
Many thanks
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Glad it worked for your use case. I used daxformatter.com to format it better and added comments to show how it works.
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.