Date.StartOfMonth Function in Power Query M (4 Examples)

Date.StartOfMonth returns the first day of the month for a given date. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you want to snap any date back to the first of its month, so you can group or label rows by month, this is the function you reach for.

Table of Contents

Syntax of Date.StartOfMonth Function

Date.StartOfMonth(dateTime as any) as any

where

dateTime (required, any). A date, datetime, or datetimezone value whose month start you want.

Returns: the first day of the month that contains dateTime, as the same kind of value you passed in. A datetime input returns midnight on the 1st.

In plain terms, you give it any date in a month and it hands back the 1st of that month.

Example 1: Get the first day of the month for a date

Take a date partway through the month and snap it to the 1st.

Date.StartOfMonth(#date(2025,8,14))

Result: 2025-08-01

August 14th sits inside August, so the function returns August 1st.

Example 2: It works no matter which day you pass

Date.StartOfMonth(#date(2025,11,30))

Result: 2025-11-01

Even on the last day of the month, the result is still the 1st. The day part of the input never changes the answer.

Example 3: A datetime input returns the 1st at midnight

Date.StartOfMonth(#datetime(2025,2,18,15,45,0))

Result: 2025-02-01

With a datetime input the result is a datetime at midnight (2025-02-01 00:00:00), so the time portion is zeroed out.

Example 4: Bucket transactions by month start

The most common use is adding a month-start column so you can group rows by month.

Say you have a Txns query with a TxnID and a TxnDate column.

Here is the starting data:

TxnID	TxnDate
T1	2025-03-17
T2	2025-03-29
T3	2025-04-02

Now add a MonthStart column with Date.StartOfMonth([TxnDate]):

let
    Source = Excel.CurrentWorkbook(){[Name="Txns"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"TxnDate",type date}}),
    Result = Table.AddColumn(Typed,"MonthStart",each Date.StartOfMonth([TxnDate]),type date)
in
    Result

The result has a clean month-start value for every row:

TxnID	TxnDate	MonthStart
T1	2025-03-17	2025-03-01
T2	2025-03-29	2025-03-01
T3	2025-04-02	2025-04-01

Both March rows collapse to 2025-03-01, which makes MonthStart an easy key to group on.

Things to keep in mind with Date.StartOfMonth

A datetime input zeroes the time. You get the 1st at midnight, not just a bare date (Example 3). Same idea applies to a datetimezone value, which keeps its offset.
Pair it with Date.EndOfMonth for a full-month range. Use Date.StartOfMonth for the lower bound and Date.EndOfMonth for the upper bound to filter a whole month.
The input must be a real date or datetime, not text. A text value like "2025-08-14" throws an Expression.Error. Convert it first by turning text into a date with Date.FromText.
It makes a clean monthly group key. Add a Date.StartOfMonth column, then group on it to total or count rows per month. To show the month as a label, format the date as text with Date.ToText.

Performance and query folding

Against a foldable source like SQL Server, a Date.StartOfMonth step can fold to the source so the work runs in the database. On Excel, CSV, or folder sources it runs locally, which is cheap even across thousands of rows.

Common questions about Date.StartOfMonth

What is the difference between Date.StartOfMonth and Date.EndOfMonth?

Date.StartOfMonth returns the first day of the month and Date.EndOfMonth returns the last day. Use them together to bound a full month, for example when filtering rows between the two.

How do I group rows by month?

Add a column with Date.StartOfMonth([YourDate]), then run Table.Group on that new column. Every row in the same month shares one start value, so they fall into the same group. The same month-start key is handy when you calculate date differences in Power Query and need to compare periods.

List of All Power Query Functions

Related Power Query Functions / Articles: