Need Help with Custom Week of month number

[vacacelaivan]

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

[v-frfei-msft · Accepted 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

[abbasabdulla]

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)

[vacacelaivan]

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?!?

[abbasabdulla]

Try using this function

= 1 + WEEKNUM ( Dates[Date],2)-WEEKNUM( DATE(YEAR(Dates[Date]),MONTH(Dates[Date]),1),2)