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 all, I've been trying to get around this problem with no luck.
Trying to create a column for the week number of the month, with the week always starting on Monday and ending on a Sunday.
Months will have 4 or 5 weeks
The below DAX almost works:
weekinmonth = 1 + WEEKNUM ( Dates[Date],21)-WEEKNUM( STARTOFMONTH (Dates[Date]),21)
However, if the month does not end on a Sunday, the week number changes back to 1 in the middle of the week which is incorrect.
I was unable to upload a screenshot at work, but in the example below Feb 01 - 04 (in red) should be in Week 5, while Monday Feb 05 should be week 1 (in blue).
Date | Week of Month |
Monday, Jan 22, 2018 | 4 |
Tuesday, Jan 23, 2018 | 4 |
Wednesday, Jan 24, 2018 | 4 |
Thursday, Jan 25, 2018 | 4 |
Friday, Jan 26, 2018 | 4 |
Saturday, Jan 27, 2018 | 4 |
Sunday, Jan 28, 2018 | 4 |
Monday, Jan 29, 2018 | 5 |
Tuesday, Jan 30, 2018 | 5 |
Wednesday, Jan 31, 2018 | 5 |
Thursday, Feb 01, 2018 | 1 |
Friday, Feb 02, 2018 | 1 |
Saturday, Feb 03, 2018 | 1 |
Sunday, Feb 04, 2018 | 1 |
Monday, Feb 05, 2018 | 2 |
Any help on this is extremely appreciated.
Ivan
Solved! Go to Solution.
Hi @vacacelaivan,
Based on my test, it is not possible to meet your requirement currently. You can come up a new idea about that and add your comments there to improve Power BI and make this feature coming sooner.
Regards,
Frank
Hi @vacacelaivan,
Based on my test, it is not possible to meet your requirement currently. You can come up a new idea about that and add your comments there to improve Power BI and make this feature coming sooner.
Regards,
Frank
Hi,
Does changing 21 to 2 in your function solve this? Why you put the 21?
weekinmonth = 1 + WEEKNUM ( Dates[Date],2)-WEEKNUM( STARTOFMONTH (Dates[Date]),2)
I tried both methods WEEKNUM (Date, 2) and WEEKNUM (DATE, 21), but both don't work in this case.
The parameter 21 is to return weeknumbers according to ISO 8601.http://blog.gbrueckl.at/2012/04/iso-8601-week-in-dax/ and a number of other articles in this forum.
Bump?!?
Try using this function
= 1 + WEEKNUM ( Dates[Date],2)-WEEKNUM( DATE(YEAR(Dates[Date]),MONTH(Dates[Date]),1),2)
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 |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |