Date.StartOfWeek Function in Power Query M (5 Easy Examples)

If you want to find the first day of the week that a given date falls in, the Date.StartOfWeek function is the one to use. Give it a date and it returns the week-start date for that week. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

Table of Contents

Syntax of Date.StartOfWeek Function

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

where

dateTime (required, any). The value whose week start 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 first day of the week that contains dateTime.

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

Example 1: Get the start of the week for a date

Take March 18, 2026, a Wednesday, and find the start of its week.

let
    Source = Date.StartOfWeek(#date(2026,3,18))
in
    Source

Result: 2026-03-15

With no firstDayOfWeek set, the week runs Sunday through Saturday. March 18 falls in the week of Sunday March 15, so the function returns that Sunday.

Example 2: Start the week on Monday instead

Most business and ISO-style reports treat Monday as day one. Pass Day.Monday as the second argument to move the boundary.

let
    Source = Date.StartOfWeek(#date(2026,3,18),Day.Monday)
in
    Source

Result: 2026-03-16

Same input, different answer. The week containing March 18 now starts on Monday March 16.

The Day.* constants run from Day.Sunday (0) through Day.Monday (1), Day.Tuesday (2), Day.Wednesday (3), Day.Thursday (4), Day.Friday (5), to Day.Saturday (6).

Example 3: Pass a datetime and watch the time reset to midnight

Date.StartOfWeek also accepts a datetime. The result is the week-start day with the time set to 00:00:00, not your original time of day.

let
    Source = Date.StartOfWeek(#datetime(2026,8,20,14,45,10)),
    Result = DateTime.ToText(Source, [Format="yyyy-MM-dd HH:mm:ss", Culture="en-US"])
in
    Result

Result: 2026-08-16 00:00:00

The DateTime.ToText step is only there to display the value as text. The actual output of Date.StartOfWeek here is a datetime for August 16, 2026 at midnight.

Notice the 14:45:10 from the input is gone. The time always resets to midnight, no matter what time you pass in.

Example 4: Add a WeekStart column to an orders table

The most common real-world use is tagging each row of a table with the week it belongs to. Say you have an Orders table and want a WeekStart column for Monday-based weeks.

Here is the starting data:

OrderID	OrderDate	Amount
ORD-501	2026-04-07	250
ORD-502	2026-04-09	180
ORD-503	2026-04-14	320
ORD-504	2026-04-17	95
ORD-505	2026-04-21	410

Type the OrderDate column as date first with Table.TransformColumnTypes, then add the WeekStart column with Table.AddColumn:

let
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"OrderDate", type date},{"Amount", Int64.Type}}),
    AddWeekStart = Table.AddColumn(Typed,"WeekStart",each Date.StartOfWeek([OrderDate],Day.Monday),type date),
    Result = Table.SelectColumns(AddWeekStart,{"OrderID","OrderDate","WeekStart"})
in
    Result

Every row gets the Monday that opens its own week.

The result keeps the order, its date, and the calculated week start:

OrderID	OrderDate	WeekStart
ORD-501	2026-04-07	2026-04-06
ORD-502	2026-04-09	2026-04-06
ORD-503	2026-04-14	2026-04-13
ORD-504	2026-04-17	2026-04-13
ORD-505	2026-04-21	2026-04-20

Orders from the same week end up with the same WeekStart value. That is what makes it a clean key for grouping or for joining to a calendar table.

Example 5: Total daily revenue by week

Once every row has a week start, weekly totals are one Table.Group step away. Say you track daily revenue and want one row per week.

Here is the starting data:

SaleDate	Revenue
2026-04-06	540
2026-04-08	610
2026-04-10	480
2026-04-15	720
2026-04-16	650

Add the WeekStart column, then group on it and sum Revenue:

let
    Source = Excel.CurrentWorkbook(){[Name="WeeklySales"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"SaleDate", type date},{"Revenue", Int64.Type}}),
    AddWeekStart = Table.AddColumn(Typed,"WeekStart",each Date.StartOfWeek([SaleDate],Day.Monday),type date),
    Grouped = Table.Group(AddWeekStart,{"WeekStart"},{{"TotalRevenue",each List.Sum([Revenue]),Int64.Type}})
in
    Grouped

Five daily rows collapse into two weekly rows:

WeekStart	TotalRevenue
2026-04-06	1630
2026-04-13	1370

The April 6, 8, and 10 sales all belong to the week starting Monday April 6, so their revenue sums to 1630. The other two rows make up the week of April 13.

Things to keep in mind with Date.StartOfWeek

A date that already is the week start comes back unchanged. The function never jumps back to the previous week, so a Sunday input with default settings returns itself.
The input must be a real date value, not text. Text like "2026-03-18" raises an Expression.Error. Type the column to date first (Examples 4 and 5) or convert it with Date.FromText.
A datetimezone input keeps its timezone offset. The time still resets to midnight, but the offset you passed in is preserved in the result.
Passing null as firstDayOfWeek is the same as leaving it out. The week starts on Sunday either way.

Common questions about Date.StartOfWeek

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

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

Can I get the start of the current week?

Yes. DateTime.LocalNow gives the current datetime, so Date.StartOfWeek(Date.From(DateTime.LocalNow())) returns this week’s start as a date.

List of All Power Query Functions

Related Power Query Functions / Articles: