Downloads
DAXTime intelligence: first and lastIntermediate

ENDOFYEAR

Returns the last date of the year that contains the dates in the filter context, with an optional fiscal year end, as a one-row table.

Syntax
ENDOFYEAR(<dates> [, <year_end_date>])

Returns: A table with one date, used as a filter inside CALCULATE.

What it does

ENDOFYEAR returns the last day of the year in context. With a year end date, it returns the last day of the fiscal year, such as June 30. Inside CALCULATE it limits the measure to that day, which gives the balance at the close of the year. Finance teams use it for year-end balances used in the audit, the annual report and the opening balance of the next year.

Syntax and arguments

Example

A company with a June 30 year end reads the cash balance at the fiscal year end for the annual summary.

Cash at Fiscal Year End =
CALCULATE(
    SUM('Bank_Balances'[Balance]),
    ENDOFYEAR('Calendar'[Date], "6/30")
)

In any month of fiscal 2027, the filter is June 30, 2027. The measure returns the cash balance on that day, which will be the closing balance for the year once the year is over.

Example: a second, different use

The calendar year end balance of receivables, for a calendar-year company's annual report.

AR at Year End = CALCULATE(SUM('AR_Snapshot'[Balance]), ENDOFYEAR('Calendar'[Date]))

Things to watch

In business terms

ENDOFYEAR supports the year-end balance sheet, the audit balances, the year-end cash and equity in the annual report, and the opening balances for the next year. The year-end date must match the fiscal year end written in the company's policies.

Related functions

All DAX functions · View as Markdown · DAX fundamentals