Forum Discussion
Helper IIncorrect week number 2021/2022
Hello,
I have a problem with the week number, in the transition from 2021 to 2022, I get this:
For getting the week number I have followed this formula when adding a custom column, which I got from this topic: (https://community.powerbi.com/t5/Desktop/Incorrect-week-number-Trasnform-Date-to-Week-of-Year/m-p/570120#M269137)
Date.WeekOfYear([Date], Day.Sunday)
So as you can see I am using the same formula but for me it is not working, 1st of Jan which is a Saturday is considered as Week 1 of 2022 instead of Week 53 of 2021, even though my formula clearly specifies that the start of each week is on a Sunday, so 2nd of Jan should actually be week 1.
Any ideas how to fix this please ?
Thank you!
3 Replies
- amitchandak
Super User
Bratone , calculate week year and week number based on week start date , new columns in date table
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1 // Monday start
Week Year = year([Week Start Date])
min week start of year = minx(filter('Date',[Year] =earlier([Year])),[Week Start date])
week No = quotient(datediff([min week start of year],[date],day),7)+1
BratoneFollowed your formulas and I get the same thing, 1st of Jan is still WK1 (instead of WK53 of 2021) and 2nd of Jan is still WK2.
The only thing that I changed was in the first column, I used:
Week Start Date = 'Calendar'[Date]+-1*WEEKDAY('Calendar'[Date],1)+1That is because I want the start of week to be on a Sunday, not on a Monday, hence the 1 instead of 2 in the weekday formula.
Syndicate_AdminAdministrator
Over return_type,We usually use only 1 or 2 to indicate that the week starts on Sunday or Monday, but by default the system assumes that the first week will be the one that contains January 1, but the ISO 8601 calendar standard says that the first week of the year is the one that contains 4 days or more. If you must use a "21" as a return_type , anything you put dierente to 21 will go with the first system.Week = WEEKNUM('Calendar Table'[Date],21)
