Functions for calculating ISO year and week number for a date (Power Query)

 2022-07-25    Power Query    0    251

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