OPENINGBALANCEYEAR
Evaluates a balance at the start of the year in the filter context, with an optional fiscal year end.
OPENINGBALANCEYEAR(<expression>, <dates> [, <filter>] [, <year_end_date>])Returns: A value (a scalar).
What it does
OPENINGBALANCEYEAR returns a balance as it stood at the start of the year, which for a fiscal year means the start of the fiscal year. For a company with a June 30 year end, the opening balance for fiscal 2027 is the balance on June 30, 2026. Finance teams use it for the annual roll-forward of equity, cash, fixed assets and inventory, and for the opening balance in the audited financial 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 builds the annual roll-forward of its bank balance. The opening balance for each year comes from the daily balance table.
Cash Opening Year = OPENINGBALANCEYEAR(SUM('Bank_Balances'[Balance]), 'Calendar'[Date])
In a 2027 row, the measure returns the balance on the first day of 2027, which is the same as the closing balance of 2026. The annual roll-forward then shows the opening, the inflows, the outflows and the closing balance.
Example: a second, different use
For a June 30 fiscal year, the year end argument goes last, after the optional filter. The balance shown is the balance at the start of the fiscal year that contains the report date.
Equity Opening FY =
OPENINGBALANCEYEAR(
SUM('Equity_Balances'[Amount]),
'Calendar'[Date],
ALL('Equity_Balances'[Entity]),
"6/30"
)
Things to watch
- The fiscal year end must match the company's year end, and it is text in month/day form.
- Keep the argument order: expression, dates, filter, then year end. The example uses ALL on the entity column, which removes any entity filter, so the balance covers every entity.
- The function needs a balance row for the opening date. A snapshot that starts later than the year start leaves a blank.
- Check the roll-forward: the opening of one year must equal the closing of the year before.
In business terms
OPENINGBALANCEYEAR supports the annual roll-forward of cash, equity and fixed assets, the opening balance check for the year-end audit, and the fiscal year reporting that a board or lender expects. It ties the statements from one year to the next.