Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
19 | |
19 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |