Calender table with Date.WeekOfYear = week 53

[StefanBosman]

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!

[Anonymous]

Hi Stefan, 

I used 

Week= WEEKNUM ('Date', 21)

It works for me.

[AlB]

@vdE 

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 

[vdE]

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?

[StefanBosman]

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. 

[AlB · Accepted solution]

@StefanBosman 

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 

[StefanBosman]

Hi @AlB ,

Thanks! Had a hard time implementing the solution, but is working!

Strange that Power Query does not support ISO week numbers...

[Anonymous]

Hi @StefanBosman 

May I ask how you solved it? I am having the same issue.

[StefanBosman]

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.

[AlB]

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 

[StefanBosman]

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.