Date.StartOfMonth
The first day of the month a date falls in.
Group: Dates and times. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
Date.StartOfMonth(dateTime as any) as any| Parameter | Type | Description |
|---|---|---|
dateTime | any | A date, datetime or datetimezone value; null gives null. |
Returns
any. The same kind of value, on the 1st; a time of day becomes midnight.
Remarks
- For a datetime or datetimezone the answer is at midnight, 00:00:00, with any offset kept.
- A month's first day makes a good key for grouping sales by month.
Examples
The month of an order
Date.StartOfMonth(#date(2024, 3, 15))#date(2024, 3, 1)Sales by month
let
Sales = #table(type table [Date = date, Amount = number], {{#date(2024, 3, 2), 120}, {#date(2024, 3, 27), 80}, {#date(2024, 4, 5), 200}}),
WithMonth = Table.AddColumn(Sales, "Month", each Date.StartOfMonth([Date]), type date)
in
Table.Group(WithMonth, {"Month"}, {{"Amount", each List.Sum([Amount]), type number}})| Month | Amount |
|---|---|
| 2024-03-01 | 200 |
| 2024-04-01 | 200 |
Related functions
Date.EndOfMonth, Date.StartOfQuarter, Date.StartOfYear, Date.AddMonths.