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.
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
- dates: the date column of the Calendar table. Required.
- year_end_date: the fiscal year end as text, such as "6/30". Optional. Leave it out for a calendar year.
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
- ENDOFYEAR picks one day. Use TOTALYTD or DATESYTD for a total.
- The year end text must match the company's fiscal year end, or the balance is read on the wrong day.
- The last day of the year must have a row in the balance table. A missing row gives a blank.
- For a year that has not ended yet, the value is the balance on a future date and may be blank. Label the figure as a forecast or use the latest actual.
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.