Date.EndOfYear
Returns December 31 of the year that contains a date.
Date.EndOfYear(dateTime as any) as dateReturns: December 31 of that year as a date, or null when the input is null.
What it does
Date.EndOfYear returns December 31 of the calendar year that holds a date. It is the balance sheet date for a calendar year company, the cut off for annual tax, and the end of an annual range. A fiscal year end is different and must be calculated from the fiscal start.
Syntax and arguments
- dateTime: a date or a datetime value.
Example
An annual report is dated to the calendar year end. Each transaction is tagged with the year end it falls under, so the year's results are grouped correctly.
let
Entries = #table(type table [Entry = text, EntryDate = date],
{{"A-1", #date(2026, 2, 14)}, {"A-2", #date(2026, 10, 29)}}),
YearEnd = Table.AddColumn(Entries, "YearEnd", each Date.EndOfYear([EntryDate]), type date),
Grouped = Table.Group(YearEnd, {"YearEnd"}, {{"Entries", each Table.RowCount(_), Int64.Type}})
in
Grouped
Both entries fall in 2026, so they group under December 31, 2026 with an Entries count of 2.
Example: a second, different use
Find the fiscal year end for a company with a July start, by taking the day before the next fiscal year starts: June 30 of the following calendar year.
Things to watch
- This is the calendar year end. A fiscal year that ends on June 30 needs its own calculation.
- The result is a date with no time part.
- A year end falls on a fixed date, so it does not move with weekends. Business day rules are a separate step.
- Null in gives null out.
In business terms
Year end is the date for the balance sheet, the annual accounts and most tax filings for calendar year companies. Having a consistent year end date on each row keeps the annual close grouped correctly.