Date.EndOfWeek
DateReturns the last day of the week containing the given date.
Syntax
Date.EndOfWeek(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 datetime or datetimezone representing the last instant of the week containing the input date.
Remarks
Date.EndOfWeek returns the last instant (23:59:59.9999999) of the calendar week containing the input value. By default, weeks start on Sunday (Day.Sunday = 0), making Saturday the last day of the week. Pass Day.Monday (1) to use ISO 8601-style Monday-to-Sunday weeks, where Sunday is the last day.
Always use the Day.* enum constants (e.g., Day.Monday, Day.Sunday) rather than integer literals when specifying firstDayOfWeek. This makes the intent explicit and avoids confusion, since 0 means Sunday while 1 means Monday.
The return type matches the input type: a plain date input produces a datetime result with the time component at 23:59:59.9999999; a datetimezone input preserves the timezone offset. Pair with Date.StartOfWeek to define an inclusive weekly range for filtering or grouping.
Examples
Example 1: Find the end of the week for each order date (Sunday start)
Table.AddColumn(
Table.SelectColumns(
Table.FirstN(Sales, 5),
{"OrderID", "OrderDate"}
),
"WeekEnd", each Date.EndOfWeek([OrderDate], Day.Sunday), type datetime
)OrderID | OrderDate | WeekEnd | |
|---|---|---|---|
| 1 | 1 | 1/15/2024 | 1/20/2024 11:59:59 PM |
| 2 | 2 | 1/18/2024 | 1/20/2024 11:59:59 PM |
| 3 | 3 | 2/1/2024 | 2/3/2024 11:59:59 PM |
| 4 | 4 | 2/10/2024 | 2/10/2024 11:59:59 PM |
| 5 | 5 | 3/5/2024 | 3/9/2024 11:59:59 PM |
Example 2: End of ISO week (Monday start)
#table(
type table [Date = date, ISOWeekEnd = datetime],
{{#date(2024, 3, 13), Date.EndOfWeek(#date(2024, 3, 13), Day.Monday)}}
)Date | ISOWeekEnd | |
|---|---|---|
| 1 | 3/13/2024 | 3/17/2024 11:59:59 PM |
Example 3: Group orders by their week ending date
Table.AddColumn(
Table.SelectColumns(Sales, {"OrderID", "OrderDate", "UnitPrice", "Quantity"}),
"WeekEndingDate",
each Date.From(Date.EndOfWeek([OrderDate], Day.Monday)),
type date
)OrderID | OrderDate | UnitPrice | Quantity | WeekEndingDate | |
|---|---|---|---|---|---|
| 1 | 1 | 1/15/2024 | 25 | 4 | 1/21/2024 |
| 2 | 2 | 1/18/2024 | 50 | 2 | 1/21/2024 |
| 3 | 3 | 2/1/2024 | 15 | 10 | 2/4/2024 |
| 4 | 4 | 2/10/2024 | 75 | 1 | 2/11/2024 |
| 5 | 5 | 3/5/2024 | 25 | 6 | 3/10/2024 |
| 6 | 6 | 3/12/2024 | 120 | 1 | 3/17/2024 |
| 7 | 7 | 4/1/2024 | 50 | 3 | 4/7/2024 |
| 8 | 8 | 4/15/2024 | 15 | 8 | 4/21/2024 |