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

 2022-07-25    Power Query    0   

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



Leave a comment:

Your comment will only be published after it has been moderated and found spam free.
Your e-mail address will only be used to display your Gravatar.