Downloads
Power Query MDateBeginner

Date.Year

Returns the calendar year of a date or datetime.

Syntax
Date.Year(dateTime as any) as nullable number

Returns: 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

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

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.

Related functions

All Power Query M functions · View as Markdown · M basics