Date.Year
Returns the calendar year of a date or datetime.
Date.Year(dateTime as any) as nullable numberReturns: The four digit year as a number, or null when the input is null.
What it does
Date.Year returns the calendar year of a date. It is the first building block for a year column, a year to date filter or a prior year comparison. For a fiscal year that does not start in January, it gives the calendar year only, so the fiscal year needs an adjustment, which the fiscal pattern on this page shows.
Syntax and arguments
- dateTime: a date or a datetime value.
Example
A general ledger extract is grouped by year, so the controller can compare this year's postings with last year's.
let
Postings = #table(type table [Account = text, PostingDate = date, Amount = number],
{{"4010", #date(2025, 11, 14), 900}, {"4010", #date(2026, 3, 2), 1100}, {"4010", #date(2026, 10, 29), 1250}}),
WithYear = Table.AddColumn(Postings, "Year", each Date.Year([PostingDate]), Int64.Type),
ByYear = Table.Group(WithYear, {"Year"}, {{"Revenue", each List.Sum([Amount]), type number}})
in
ByYear
The 2025 row holds 900, and the 2026 rows total 2,350. The year is the calendar year, so both 2026 postings fall in the same group.
Example: a second, different use
Compare two periods by using Date.Year on each date and filtering to the same year, rather than writing two separate queries.
Things to watch
- Date.Year returns the calendar year. A fiscal year starting in July needs an offset, shown on the Date.AddMonths page.
- Text dates must be converted first with Date.FromText.
- A date that is null returns null, and a group with a null year appears as its own row.
- The result is a number. Set Int64.Type on the new column so it sorts as a number.
In business terms
Year columns drive year over year comparisons, annual reports and the year-end close. Being precise about calendar year versus fiscal year is the most common source of a wrong comparison in a finance model.