Date.Month
Returns the calendar month number of a date or datetime, from 1 for January to 12 for December.
Date.Month(dateTime as any) as nullable numberReturns: The month as a number from 1 to 12, or null when the input is null.
What it does
Date.Month returns the month of a date as a number. It is the basis of most monthly summaries, and it sorts correctly, where a month name would sort alphabetically. Month 1 is January and month 12 is December, so a fiscal month needs an offset to match a fiscal calendar.
Syntax and arguments
- dateTime: a date or a datetime value.
Example
A monthly revenue table is built from a list of invoice dates. The month number is used to sort the months in the right order, and the name is added only for display.
let
Invoices = #table(type table [InvoiceDate = date, Amount = number],
{{#date(2026, 2, 14), 400}, {#date(2026, 10, 1), 900}, {#date(2026, 2, 27), 150}}),
WithMonth = Table.AddColumn(Invoices, "MonthNumber", each Date.Month([InvoiceDate]), Int64.Type),
Monthly = Table.Group(WithMonth, {"MonthNumber"}, {{"Billed", each List.Sum([Amount]), type number}}),
Sorted = Table.Sort(Monthly, {{"MonthNumber", Order.Ascending}})
in
Sorted
February has two invoices totaling 550 and October has 900. The rows sort in calendar order, from month 2 to month 10, not in alphabetical order by name.
Example: a second, different use
Use the month of the close date to label a month end accrual for the period it belongs to.
Things to watch
- Month numbers do not include the year. Combine with Date.Year for a period key, such as 202610, or the same month in two years will merge.
- A fiscal month needs an offset. For a July fiscal start, Date.Month(Date.AddMonths(date, 6)) gives the fiscal month.
- Use the number for sorting and the name for display, as shown on the Date.MonthName page.
- Null in gives null out.
In business terms
Monthly reports, budget versus actual tables and trend charts are built from the month number. Keeping the number and the year together is what keeps a 12 month view correct across a year boundary.