Downloads
Power Query MDateBeginner

Date.StartOfMonth

Returns the first day of the month that contains a date.

Syntax
Date.StartOfMonth(dateTime as any) as date

Returns: The first day of the month as a date, or null when the input is null.

What it does

Date.StartOfMonth moves any date back to the first day of its month. It is used to group daily data into months, to build a period start date for a report, and to set a range for a monthly filter. Combined with Date.EndOfMonth, it gives the full span of a month.

Syntax and arguments

Example

A monthly rent accrual is booked on the first day of each month. The accrual date for any posting is set to the first day of its month, so it appears in the right period.

let
    Postings = #table(type table [Vendor = text, PostedOn = date, Amount = number],
        {{"Harbor Realty", #date(2026, 10, 29), 4200}, {"Harbor Realty", #date(2026, 11, 3), 4200}}),
    Accrual = Table.AddColumn(Postings, "PeriodStart", each Date.StartOfMonth([PostedOn]), type date)
in
    Accrual

October 29 gives a period start of October 1, 2026. November 3 gives November 1, 2026. Each posting is now tied to the month it belongs to.

Example: a second, different use

Filter a table to the current month by comparing each date with the start of the month and the end of the month.

Things to watch

In business terms

Period starts drive accruals, prepayments and monthly allocations. A standard period start on every row makes the month-end entries line up with the period they affect.

Related functions

All Power Query M functions · View as Markdown · M basics