Custom week per month

[Selded]

I have a date table with week number based on the calender year. Each week starts from monday and ends on sunday. I want to create a custom week number based on months, each month will have week1, 2, 3,4,5.

Below is a sample of my date table. 

Thanks for the assistance.

[amitchandak · Accepted solution]

@Selded , Try new columns like. You might have some the dependent colums with different name

Start Month = STARTOMONTH('Date'[Date])
WeekDay = WEEKDAY([Date],2) //monday
Start of Week = [Date] -[WeekDay]+1 //monday
Month Week = QUOTIENT(DATEDIFF(Minx(FILTER('Date',[Start Month]=EARLIER([Start Month])),'Date'[Start of Week]),[Date],DAY),7)+1

[vanessafvg · Accepted solution]

in power query under transform ribbon bar, select  date (has a calendar looking thing) drop down to  week from the menu and you should be able to choose week of month there, duplicate your date column before you do this.

[vanessafvg · Accepted solution]

in power query under transform ribbon bar, select  date (has a calendar looking thing) drop down to  week from the menu and you should be able to choose week of month there, duplicate your date column before you do this.

[Selded · Accepted solution]

@vanessafvg 

This worked , but unfortuantely my date table has week day starting on monday instead of Sunday. how do i edit the start day of my week to get this sorted correctly.

[vanessafvg]

here is good article explaining how to adjust that There is a function called StartOfWeek that displays the start of the week in Power Query formula language.

Syntax: Date.StartOfWeek(<date>)

The week starts on Sunday and runs through to Saturday. To use Monday to Friday, you can add a day to it using the function Date.AddDays(<date>, 1).

https://carldesouza.com/power-query-language-m-start-week/

then you can write code in the custom column to adjust things the way you want to...

[Selded]

@vanessafvg @amitchandak 

Thank you both, i got the week numbers correctly. 

please is there a way to for the column to read week 1 , week 2, instead of 1,2, 3.

Thank you 

[vanessafvg]

just create a new column concatenating"week" + week no.

[amitchandak · Accepted solution]

@Selded , Try new columns like. You might have some the dependent colums with different name

Start Month = STARTOMONTH('Date'[Date])
WeekDay = WEEKDAY([Date],2) //monday
Start of Week = [Date] -[WeekDay]+1 //monday
Month Week = QUOTIENT(DATEDIFF(Minx(FILTER('Date',[Start Month]=EARLIER([Start Month])),'Date'[Start of Week]),[Date],DAY),7)+1

[Selded]

@amitchandak 

Thanks, this fixes the issue.