Date.StartOfMonth
Returns the first day of the month that contains a date.
Date.StartOfMonth(dateTime as any) as dateReturns: 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
- dateTime: a date or a datetime value.
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
- The time part is removed for a datetime input, so the result is a date.
- The result is the first day, not the last. Use Date.EndOfMonth for the last day.
- Month starts are good group keys. Joining on them needs both sides to be dates, not text.
- Null in gives null out.
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.