Forum Discussion
Custom week per month
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.
Anonymous , 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)+1in 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.
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.
7 Replies
- amitchandak
Super User
Anonymous , 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
Community Champion
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.
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.
- vanessafvghere 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...