Incorrect week number at year end / beginning

Anonymous ,

You also have a solution to calculate ISO Week Number (First week of 4 days), using M code to add to your code creating your date table :

//Calculate ISO Week Num in 4 steps 
//Step1. Calculate the date of the Thursday of the week 
InsertCurrThursday = Table.AddColumn(InsertMonthEnding, "CurrThursday", each Date.AddDays([DateRef], -Date.DayOfWeek([DateRef],1) + 3), type date),
//Step2. Calculate the 1st january of the date (cf step1) 
InsertFirstJan = Table.AddColumn(InsertCurrThursday, "FirstJan", each #date(Date.Year([CurrThursday]),1,1),type date),
//Step3. Calculate the number of days between the 1st january and Thursday
InsertDuration= Table.AddColumn(InsertFirstJan, "Duration", each Duration.Days(Duration.From([CurrThursday] - [FirstJan])), type number),
//Step4. Divide the number of days (duration)calculated on step3 by 7
//Roud it down and add 1
InsertISOWeekNum = Table.AddColumn(InsertDuration, "NumSemISO", each Number.RoundDown([Duration]/7)+1), ChangeType5=Table.TransformColumnTypes(InsertISOWeekNum,{{"NumSemISO", Int64.Type}}),
//Delete unusefull columns 
RemovedColumns = Table.RemoveColumns(ChangeType5, {"CurrThursday","FirstJan","Duration"})

 I'm using the rules defined by the ISO norm :

For further details :

https://en.wikipedia.org/wiki/ISO_week_date

Hope it helps

Hi,

In your first screenshot, it seems that weeks number are sorted like text, so I think that if you format them as numbers, or use 01 instead of 1 it should do the trick.

Screenshot number is more strange, do you sort it by week numbers ?

Have a nice day,