Date.StartOfWeek
DateReturns the first day of the week containing the given date.
Syntax
Date.StartOfWeek(dateTime as any, optional firstDayOfWeek as nullable number) as anyParameters
| Name | Type | Required | Description |
|---|---|---|---|
dateTime | any | Yes | A date, datetime, or datetimezone value. |
firstDayOfWeek | number | No | A Day.* constant specifying the first day of the week. Defaults to Day.Sunday (0). Use Day.Monday (1) for ISO weeks. |
Return Value
any — A date, datetime, or datetimezone value representing the first day of the week.
Remarks
Date.StartOfWeek returns the first day of the calendar week containing the input value. By default, weeks start on Sunday (Day.Sunday = 0). Pass Day.Monday (1) for ISO 8601-style Monday-to-Sunday weeks. The return type matches the input type: a plain date input returns a date; a datetime or datetimezone input returns the same type with the time component set to midnight of the week-start day.
Always use the Day.* enum constants (e.g., Day.Monday, Day.Sunday) rather than integer literals for the firstDayOfWeek parameter. This makes code self-documenting and avoids confusion.
Pair Date.StartOfWeek with Date.EndOfWeek using the same firstDayOfWeek value to build consistent weekly range filters. Date.StartOfWeek is also used as a grouping key for weekly aggregations — group by Date.StartOfWeek([OrderDate], Day.Monday) to bucket all orders into their ISO week.
Examples
Example 1: Add the week start date to each order
Table.AddColumn(
Table.SelectColumns(
Table.FirstN(Sales, 5),
{"OrderID", "OrderDate"}
),
"WeekStart", each Date.StartOfWeek([OrderDate], Day.Sunday), type date
)OrderID | OrderDate | WeekStart | |
|---|---|---|---|
| 1 | 1 | 1/15/2024 | 1/14/2024 |
| 2 | 2 | 1/18/2024 | 1/14/2024 |
| 3 | 3 | 2/1/2024 | 1/28/2024 |
| 4 | 4 | 2/10/2024 | 2/4/2024 |
| 5 | 5 | 3/5/2024 | 3/3/2024 |
Example 2: Start of ISO week (Monday start)
#table(
type table [Date = date, ISOWeekStart = date],
{{#date(2024, 3, 13), Date.StartOfWeek(#date(2024, 3, 13), Day.Monday)}}
)Date | ISOWeekStart | |
|---|---|---|
| 1 | 3/13/2024 | 3/11/2024 |
Example 3: Group sales by week start for weekly aggregation
Table.AddColumn(
Table.SelectColumns(Sales, {"OrderID", "OrderDate", "UnitPrice", "Quantity"}),
"WeekStarting",
each Date.StartOfWeek([OrderDate], Day.Monday),
type date
)OrderID | OrderDate | UnitPrice | Quantity | WeekStarting | |
|---|---|---|---|---|---|
| 1 | 1 | 1/15/2024 | 25 | 4 | 1/15/2024 |
| 2 | 2 | 1/18/2024 | 50 | 2 | 1/15/2024 |
| 3 | 3 | 2/1/2024 | 15 | 10 | 1/29/2024 |
| 4 | 4 | 2/10/2024 | 75 | 1 | 2/5/2024 |
| 5 | 5 | 3/5/2024 | 25 | 6 | 3/4/2024 |
| 6 | 6 | 3/12/2024 | 120 | 1 | 3/11/2024 |
| 7 | 7 | 4/1/2024 | 50 | 3 | 4/1/2024 |
| 8 | 8 | 4/15/2024 | 15 | 8 | 4/15/2024 |