cancel
Showing results for
Did you mean:
Frequent Visitor

## Need Help with Custom Week of month number

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Need Help with Custom Week of month number

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Frequent Visitor

## Re: Need Help with Custom Week of month number

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)

Frequent Visitor

## Re: Need Help with Custom Week of month number

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

Frequent Visitor

## Re: Need Help with Custom Week of month number

Try using this function

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

## Re: Need Help with Custom Week of month number

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.