Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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.
Proud to be a Super User!
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.
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.
Proud to be a Super User!
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.
Proud to be a Super User!
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
Proud to be a Super User!
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |