Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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)
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |