Date.EndOfWeek Function in Power Query M (4 Easy Examples)

If you want to find the last day of the week that a given date falls in, the Date.EndOfWeek function is what you reach for. Give it a date and it hands back the end-of-week date for that week. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

Table of Contents

Syntax of Date.EndOfWeek Function

Date.EndOfWeek(dateTime as any, optional firstDayOfWeek as nullable number) as any

where

dateTime (required, any). The value whose end-of-week you want. It can be a date, datetime, or datetimezone, and the function returns the same kind you pass in.
firstDayOfWeek (optional, nullable number). The day the week starts on, given as a Day.* constant like Day.Monday. Omit it and the week starts on Day.Sunday.

Returns: a value of the same type you passed in (a date in, a date out), set to the last day of the week that contains dateTime.

In plain terms, you give it a date and it tells you which date closes out that week.

Example 1: End of the week with the default Sunday start

Take September 10, 2025, a Wednesday, and find the end of its week.

let
    Source = Date.EndOfWeek(#date(2025,9,10))
in
    Source

Result: 2025-09-13

With no firstDayOfWeek set, the week runs Sunday through Saturday. September 10 sits in the week of Sunday September 7 to Saturday September 13, so the function returns that Saturday.

Example 2: Change the week start with Day.Monday

The same date can land on a different end-of-week once you move the week start. Pass Day.Monday so the week runs Monday through Sunday.

let
    Source = Date.EndOfWeek(#date(2025,9,10),Day.Monday)
in
    Source

Result: 2025-09-14

Now the week containing September 10 is Monday September 8 to Sunday September 14, so the end-of-week is that Sunday. The input date did not change, but moving the week boundary changed the answer.

Example 3: A date that already is the end of its week

If the date you pass is already the last day of its week, the function returns it unchanged.

let
    Source = Date.EndOfWeek(#date(2025,9,13))
in
    Source

Result: 2025-09-13

September 13, 2025 is a Saturday, which is the end of the default Sunday-start week, so there is nothing to shift and you get the same date back.

Example 4: Add a WeekEnding column to a timesheet table

The most common use is rolling daily rows up to the week they belong to. Say you have a Timesheet table and want a WeekEnding date for each row so you can group hours by week.

Here is the starting data:

Employee	WorkDate	Hours
Ava Reyes	2025-01-06	8
Ava Reyes	2025-01-09	7
Liam Carter	2025-01-14	6
Liam Carter	2025-01-20	8

Type the WorkDate column as date first with Table.TransformColumnTypes, then add a WeekEnding column with Table.AddColumn:

let
    Source = Excel.CurrentWorkbook(){[Name="Timesheet"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"WorkDate", type date},{"Hours", Int64.Type}}),
    AddWeekEnding = Table.AddColumn(Typed,"WeekEnding",each Date.EndOfWeek([WorkDate]),type date),
    Result = Table.SelectColumns(AddWeekEnding,{"Employee","WorkDate","WeekEnding"})
in
    Result

Each row gets the Saturday that closes its own week.

The result keeps the employee, the work date, and the calculated week ending:

Employee	WorkDate	WeekEnding
Ava Reyes	2025-01-06	2025-01-11
Ava Reyes	2025-01-09	2025-01-11
Liam Carter	2025-01-14	2025-01-18
Liam Carter	2025-01-20	2025-01-25

Ava’s January 6 and January 9 rows both fall in the same Sunday January 5 to Saturday January 11 week, so they share a WeekEnding. That is what makes a clean grouping key.

Things to keep in mind with Date.EndOfWeek

The default firstDayOfWeek is Day.Sunday, so the default end is the Saturday (Example 1). The Day.* constants run Sunday-based from Day.Sunday (0) through Day.Monday (1), Day.Tuesday (2), Day.Wednesday (3), Day.Thursday (4), Day.Friday (5), to Day.Saturday (6).
It returns the same type you pass in. A date comes back as a date at midnight. A datetime or datetimezone comes back as the very last moment of the final day, 23:59:59.9999999.
The input must be a real date value, not text. A text column throws Expression.Error: We cannot convert a value of type Text to type Date. Type the column to date first (Example 4) or wrap the value in Date.FromText. Going the other way, use Date.ToText to format the result as a label.
It pairs with Date.StartOfWeek for the other end of the same week, so you can build a week-start and a week-end column from the same source.

Common questions about Date.EndOfWeek

What is the difference between Date.EndOfWeek and Date.StartOfWeek?

Both work on the week that contains your date. Date.EndOfWeek returns the last day of that week, while Date.StartOfWeek returns the first day. They share the same firstDayOfWeek argument, so the week boundary you pick applies to both.

List of All Power Query Functions

Related Power Query Functions / Articles: