Date.StartOfWeek
The first day of the week a date falls in.
Group: Dates and times. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
Date.StartOfWeek(dateTime as any, optional firstDayOfWeek as nullable number) as any| 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
any. The same kind of value, on the week's first day; a time of day becomes midnight.
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. - For a datetime or datetimezone the answer is at midnight, 00:00:00, with any offset kept.
Examples
The week of a Wednesday, en-US
Date.StartOfWeek(#date(2024, 3, 13))#date(2024, 3, 10)In en-US the week starts on Sunday: 10 March.
Weeks that start on Monday
Date.StartOfWeek(#date(2024, 3, 13), Day.Monday)#date(2024, 3, 11)Orders by week
let
Orders = #table({"Order", "Date"}, {{"A-101", #date(2024, 3, 11)}, {"A-102", #date(2024, 3, 14)}, {"A-103", #date(2024, 3, 18)}})
in
Table.AddColumn(Orders, "Week", each Date.StartOfWeek([Date], Day.Monday), type date)| Order | Date | Week |
|---|---|---|
| A-101 | 2024-03-11 | 2024-03-11 |
| A-102 | 2024-03-14 | 2024-03-11 |
| A-103 | 2024-03-18 | 2024-03-18 |
Related functions
Date.EndOfWeek, Date.DayOfWeek, Date.WeekOfYear, Date.AddWeeks.