Date.WeekOfYear
Which week of its year a date falls in, counting the week that holds 1 January as week 1.
Group: Dates and times. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
Date.WeekOfYear(dateTime as any, optional firstDayOfWeek as nullable number) as nullable number| Parameter | Type | Description |
|---|---|---|
dateTime | any | A date, datetime or datetimezone value; null gives null. |
firstDayOfWeek | nullable number optional | The day a week starts on: Day.Sunday (0) to Day.Saturday (6). Left out or null, the culture's first day is used. |
Returns
nullable number. 1 to 54.
Remarks
- With no first day given, the week starts on the first day of the query's culture: Sunday in en-US, Monday in Norway, Britain and Germany (nb-NO, en-GB, de-DE). Give
Day.Monday(or another day) when the answer must not depend on the culture. - These are not ISO 8601 weeks, the ones calendars in Norway and much of Europe print. Here week 1 always holds 1 January, however few of its days are in the new year; in ISO, week 1 is the one with the year's first Thursday. Near New Year the two disagree: see the second example for an ISO week number in M.
Examples
A mid-March order
Date.WeekOfYear(#date(2024, 3, 15))11The week number next to the ISO week
let
d = #date(2024, 12, 30),
thursday = Date.AddDays(d, 3 - Date.DayOfWeek(d, Day.Monday))
in
[WeekOfYear = Date.WeekOfYear(d, Day.Monday), IsoWeek = Number.IntegerDivide(Date.DayOfYear(thursday) - 1, 7) + 1][WeekOfYear = 53, IsoWeek = 1]30 December 2024 is in week 53 here and in ISO week 1 of 2025: the ISO week is counted from the Thursday of the date's week.
Related functions
Date.WeekOfMonth, Date.StartOfWeek, Date.DayOfWeek, Date.DayOfYear.