CLOSINGBALANCEYEAR
Evaluates a balance on the last date of the year in the filter context, with an optional fiscal year end.
CLOSINGBALANCEYEAR(<expression>, <dates> [, <filter>] [, <year_end_date>])Returns: A value (a scalar).
What it does
CLOSINGBALANCEYEAR returns the balance at the end of the year. With a year end date, the end is the end of the fiscal year: for a June 30 year end, the closing balance for fiscal 2027 is the balance on June 30, 2027. Finance teams use it for the year-end balance sheet, the audit balances, the year-end cash and the closing equity in the annual statements.
Syntax and arguments
- expression: the measure or column that holds the balance. Required.
- dates: the date column of the Calendar table. Required.
- filter: an optional filter, such as a legal entity. Optional. If you use a filter and a year end date, the filter comes third and the year end date fourth.
- year_end_date: the fiscal year end as text in the form month/day, such as "6/30". Optional. Leave it out for a calendar year.
Example
A company with a calendar year end wants the cash balance at December 31 for each year, as a year-end figure in the annual summary.
Year End Cash = CLOSINGBALANCEYEAR(SUM('Bank_Balances'[Balance]), 'Calendar'[Date])
In a 2026 row, the measure returns the balance on December 31, 2026. In a summary that shows several years, each row shows the year-end balance of its own year.
Example: a second, different use
For a June 30 fiscal year, the closing equity at the fiscal year end. The filter is placed before the year end argument, and ALL removes any entity filter, so the total covers every entity.
Equity Closing FY =
CLOSINGBALANCEYEAR(
SUM('Equity_Balances'[Amount]),
'Calendar'[Date],
ALL('Equity_Balances'[Entity]),
"6/30"
)
Things to watch
- The year end argument must match the company's fiscal year end, and it is text in month/day form.
- The last day of the year must have a snapshot row, or the result is blank. Check the balance table for the year-end dates.
- Use it only for balances. A sum of the year's transactions is a flow, not a balance.
- Reconcile the year-end figure to the audited balance sheet before you publish it.
In business terms
CLOSINGBALANCEYEAR supports the year-end balance sheet, the audit balances, the year-end cash position, the closing equity and inventory figures in the annual report, and the opening balance that carries into the next year.