Functions for calculating ISO year and week number for a date (Power Query)
2022-07-25 Power Query 0 520
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