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.
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!
Solved! Go to Solution.
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 Stefan,
I used
Week= WEEKNUM ('Date', 21)
It works for me.
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
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.
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 @Anonymous ,
I have used the query's from @AilleryO in the mentioned post and updated them to fit my Calender table in Power Query.
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.
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.