Okay so I think I found something that works but you’ll have to do some checking over multiple years and/or changing start of week days. So without defining custom logic, we’ll rely the standard M function to calculate the Weeknumber.
First Added a new WeekOfYear column with this logic:
So the Weeknumber is no longer bound to the [Date] but to the Date.StartOfWeek, this solves the issue where the last days of the week belonging to the next year, getting a different weeknumber.
Next to adjust the Weeknumber when weeks have spilled over into the next year. First Grouped a subset of your Date table by Year, this way we know if there is spillover from the previous year.
To account for that, added a correction value for each date in the year. So if there are spilled over dates AND the year for the Start of Week date < Year then 0 else -1
Finally added this correction value to the new WeekOfYear calculation.
Here’s your updated M code. Date_Table v2.txt (6.0 KB)
I hope this is helpful.