Month starts, month ends and period keys
Turn dates into reporting periods and calculate due dates from period boundaries.
~9 min read · intermediate · Module 7: Numbers, dates and conditional columns
By Tahir Nas · Examples run in our simulator · Updated
Before this: Date parts and durations
Reports rarely want individual dates; they want months, quarters or weeks. Rounding a date to the start of its period gives a value you can group, merge and sort on, and it stays a real date.
Start and end of a month
let
Source = #table({"Invoice", "InvoiceDate"}, {{"I-1", #date(2026, 1, 30)}, {"I-2", #date(2026, 2, 3)}, {"I-3", #date(2028, 2, 10)}}),
Periods = Table.AddColumn(Source, "MonthStart", each Date.StartOfMonth([InvoiceDate]), type date),
Ends = Table.AddColumn(Periods, "MonthEnd", each Date.EndOfMonth([InvoiceDate]), type date)
in
EndsResult
| Invoice | InvoiceDate | MonthStart | MonthEnd |
|---|---|---|---|
| I-1 | 2026-01-30 | 2026-01-01 | 2026-01-31 |
| I-2 | 2026-02-03 | 2026-02-01 | 2026-02-28 |
| I-3 | 2028-02-10 | 2028-02-01 | 2028-02-29 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Due dates from period ends
Terms like 'payment due 10 days after the end of the invoice month' are one line once you have the month end. Adding 40 days to the invoice date only works for some invoices:
let
Source = #table({"Invoice", "InvoiceDate"}, {{"I-1", #date(2026, 1, 30)}, {"I-2", #date(2026, 2, 3)}}),
Due = Table.AddColumn(Source, "Due", each Date.AddDays(Date.EndOfMonth([InvoiceDate]), 10), type date)
in
DueResult
| Invoice | InvoiceDate | Due |
|---|---|---|
| I-1 | 2026-01-30 | 2026-02-10 |
| I-2 | 2026-02-03 | 2026-03-10 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Adding months
Date.AddMonths keeps the day of the month where it can, and moves to the last valid day when it can't. 31 January plus one month is 28 February (or the 29th in a leap year), not 3 March:
#table({"Start", "Plus 1 month", "Plus 2 months"}, {{#date(2026, 1, 31), Date.AddMonths(#date(2026, 1, 31), 1), Date.AddMonths(#date(2026, 1, 31), 2)}})Result
| Start | Plus 1 month | Plus 2 months |
|---|---|---|
| 2026-01-31 | 2026-02-28 | 2026-03-31 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Sortable period keys
For a text label that sorts correctly, use year first with a padded month: "2026-03" sorts before "2026-11", but "March 2026" sorts after "April 2026". Date.ToText with a format string builds it:
Date.ToText(#date(2026, 3, 14), [Format = "yyyy-MM", Culture = "en-US"])Result
2026-03
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Quarters and years work the same way with Date.StartOfQuarter, Date.EndOfQuarter, Date.StartOfYear and Date.EndOfYear. For a fiscal year that starts in April, shift the date first: Date.Year(Date.AddMonths([Date], 9)) gives the fiscal year that ends in the following March.
Where to find this in Excel
- Excel for Windows
- Add Column > Custom Column
- What you see
- Month starts, month ends and period keys are usually written as a short formula here. The Add Column > Date menu covers the simpler parts such as Year and Month.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Add a custom column (Microsoft Learn); Add a column based on a data type (Power Query) (Microsoft Support).
Common mistakes
- Adding a fixed number of days (30, 40) to approximate month-based terms.
- Building month labels like "March 2026" and sorting on them.
- Writing the format as "yyyy-mm": lowercase mm means minutes in .NET-style format strings.
Excel, Power BI and the simulator
- Excel and Power BI Desktop: Transform → Date → Month → Start of Month / End of Month write Date.StartOfMonth and Date.EndOfMonth.
- Date.ToText format strings follow .NET conventions in both hosts; the culture decides month names and separators.
- The simulator supports common date format patterns (yyyy, MM, MMM, MMMM, dd, ddd and similar); an unsupported pattern raises an error instead of guessing.
Next step
Apply this lesson in the simulator. The exercise is checked against fresh data, so the query has to work for rows it hasn't seen. It's part of Pro.
Practise: Calculate reimbursement dates (consultancy)Practice this
Each exercise runs in the browser and is checked against fresh data, not just the example.