### 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
```