Forum Discussion
Power Query Calendar Creation - 53 weeks
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
Please see this article with a good way to handle that.
445 Calendar with 53-Week Years – Hoosier BI
Pat
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
4 Replies
Please see this article with a good way to handle that.
445 Calendar with 53-Week Years – Hoosier BI
Pat
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 IWNPlease look Power Query function for ISO Year and/or Week number (ISO 8601) with optional offset and format
NB! Pay attention to offsetindays parameter (by default it equals to zero - it means week starts on Sunday)
