How to fix calendar table for ISO week number reset every year

[Anonymous]

Hello,

I have the following calendar table. The aim is to get the correct ISO week number 8601 format, so the week start is always on Monday and the end of the week is always on the following Sunday.  The WeekSequenceNum column is correct according to the dates, with the calendar starting from 1 January 2020. But i would like to have a reset when the year changes but it should still be in line with the ISO 8601 format, where the first week of the year should be the one with the Thursday included, or any other rule that is applicable to get the ISO 8601 week number format. The problem in this case is that the week number does not follow correctly at the start of the year 2021 and 2022. 

For example, as below, the WeekSequenceNum (resets each year) should be 1 as from 4 January 2021. From 1 January 2021 until 3 January 2021, the WeekSequenceNum (resets each year) should be 53. In Power Query, the problem is the calculated value in the column "MinWeekSequenceNum" which is based off of the year dates but the start of each year 2021, 2022, etc does not correspond exactly to the first week number. So, i'm not sure how to approach or solve this problem in Power Query.

Here is the PBIX file: https://drive.google.com/file/d/1rmREtki6DVAF22t4zqeBXrq8wH6Wd6g3/view?usp=sharing

(click on the Download icon at the top right corner of the window - please let me know if there is a better way to share the file)

Any help is much appreciated!!

[Greg_Deckler · Accepted solution]

@Anonymous WEEKNUM([Date], 21)

[Anonymous]

@Greg_Deckler Thanks for the alternate solution! I created a calculated column and used the formula. It works!

But on a side note, i was hoping to resolve this entirely through Power Query. Is that possible?

[Greg_Deckler]

@Anonymous It is but not pretty, there is no native ISO week in Power Query:

ISO Week in Power Query / M language and Power BI | Excel Inside (excel-inside.pro)