Functions for calculating ISO year and week number for a date (Power Query)
2022-07-25 Power Query 0 160
ISO week numbers are used in most parts of the civilized world.
The first ISO week in a year is the week that contains the date for the 4th of January.
ISO weeks start on a Monday and end on a Sunday.
The week belongs to the year in which the date for Thursday belongs to.
//fxWeekISO //this function returns the ISO week number (InputDate as date) as number => let // Thursday in InputDate week ThursdayInputDate = Date.AddDays(InputDate, 3 - Date.DayOfWeek(InputDate, Day.Monday)), // ISO year is the year for Thursday in InputDate week YearISO = Date.Year(ThursdayInputDate), // Thursday in ISO week 1, the 4th of January is always in ISO week 1 ThursdayWeek1 = Date.AddDays(#date(YearISO, 1, 4), 3 - Date.DayOfWeek(#date(YearISO, 1, 4), Day.Monday)), // InputDate ISO week number WeekISO = Number.From(ThursdayInputDate - ThursdayWeek1) / 7 + 1 in WeekISO
//fxYearWeekISO //this function returns the ISO year and week number as yyyy-ww (InputDate as date) as text => let // Thursday in InputDate week ThursdayInputDate = Date.AddDays(InputDate, 3 - Date.DayOfWeek(InputDate, Day.Monday)), // ISO year is the year for Thursday in InputDate week YearISO = Date.Year(ThursdayInputDate), // Thursday in ISO week 1, the 4th of January is always in ISO week 1 ThursdayWeek1 = Date.AddDays(#date(YearISO, 1, 4), 3 - Date.DayOfWeek(#date(YearISO, 1, 4), Day.Monday)), // InputDate ISO week number WeekISO = Number.From(ThursdayInputDate - ThursdayWeek1) / 7 + 1, // InputDate ISO year-week YearWeekISO = Text.From(YearISO) & "-" & Text.End("00" & Text.From(WeekISO), 2) in YearWeekISO