Power Query Calendar Creation - 53 weeks

[micjensen]

Hi Community

I really need your help on how to create a calendar that automatically knows that when it comes to the beginning of 2021, this is still week 53 and not week 1. I have only managed to create a calendar where the week number starts by 1 when entering a new calendar year. But for year 2021: the 1st to the 3rd january is week 53 and then week 1 starting from january 4th.

@PowerQuery @calendar@weeknum
How do i build in this knowledge ??

I rellay hope that someone has the answer to this, t

[BA_Pete · Accepted solution]

Hi @micjensen ,

This is very complex logic to code into Power Query.

I would recommend using WEEKNUM([Date], 21) in a DAX calculated column to be honest:

Pete

[mahoneypat · Accepted solution]

Please see this article with a good way to handle that.

445 Calendar with 53-Week Years – Hoosier BI

Pat

[DenisSipchenko]

Please look Function for ISO Year/Week number (ISO 8601)) 

NB! Pay attention to offsetindays parameter (by default it equals to zero - it means week starts on Sunday)

[ronrsnfld · Accepted solution]

Here's a custom function in M code for calculating the ISO Weeknumber (if that is what you want):

//fxISOWeekNum

(theDate as date) =>
let
   a = Date.AddDays(theDate,-1),
   b = Date.DayOfWeek(a,Day.Sunday),
   c = Date.AddDays(theDate,-b + 3),
   d2 = #date(Date.Year(c),1,3),
   IWN  =  Number.IntegerDivide(Number.From(theDate)-Number.From(d2) + Date.DayOfWeek(d2)+6,7)

in
    IWN

[mahoneypat · Accepted solution]

Please see this article with a good way to handle that.

445 Calendar with 53-Week Years – Hoosier BI

Pat

[BA_Pete · Accepted solution]

Hi @micjensen ,

This is very complex logic to code into Power Query.

I would recommend using WEEKNUM([Date], 21) in a DAX calculated column to be honest:

Pete