Forum Discussion
Calender table with Date.WeekOfYear = week 53
Hi all,
I am struggeling with my Calender Table in Power Query editor. I have been using my Calender table for over a year now, but since the year 2021 my WeekOfYear is off. On the 1st of January it starting counting from week 1 again. This results in Monday the 4th of January being the start of week 2. This is incorrect! Does anyone know how to solve this?
In Power Query editor I added the column like this:
= Table.AddColumn(#"Added Custom CurWeekOffset", "Week van jaar", each Date.WeekOfYear([Date]), Int64.Type)
My dates look like this:
Obviously, December 28th till January 3rd should be week 53 and January 4th should be the start of week 1.
Thank you in advance!
Check this out
https://community.powerbi.com/t5/Desktop/Incorrect-week-number-at-year-end-beginning/m-p/1474033
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
10 Replies
Check this out
https://community.powerbi.com/t5/Desktop/Incorrect-week-number-at-year-end-beginning/m-p/1474033
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi AlB ,
Thanks! Had a hard time implementing the solution, but is working!
Strange that Power Query does not support ISO week numbers...
Hi StefanBosman
Is the "problem" only in 2021? If you want a quick and dirty solution, just assign a 53 if the day is <= than 3rd Jan and subtract one for the other days in the year, either in that same column or in a separate one
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi AlB ,
Thank you for your reply. However, I would like a permanent solution, so this would not happen again.
What query should I insert instead of the Date.WeekOfYear([Date])?
Thank you.
I can give you the formules for week 53 at the beginning of 2021.
But then we have a other problem:
If you select year & week, 1 January 2021 falls in week 53-2021.
That is incorrect.
Does someone know a solution for this?
Hi vdE ,
I haven't thought of that. I always use my Week No. and Year columns seperate from each other. I also do not have a solution for your challenge.
I do not understand the question. Can you share a sample pbix including the M code where you show what you want do and explain what doesn't work?
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
