CLOSINGBALANCEYEAR

An expression evaluated at the last date of the year in the filter context (the year ending on 31 December, or on a date given).

Group: Time intelligence. Compatible with DAX.

Syntax

CLOSINGBALANCEYEAR ( <expression>, <dates> [, <filter>] [, <year_end_date>] )

Parameter	Type	Description
`expression`	Expression	The value wanted on that one date, usually a measure of a level such as a price, a stock or a balance.
`dates`	Dates	A column of dates of the model, such as `Sales[Month]`, or the name of a calendar defined in the model.
`filter`	Filter optional	A condition or a table that filters as well, as a filter argument of `CALCULATE` does.
`year_end_date`	Text optional	The year's last day as month and day, such as "6-30" for a year that ends on 30 June; when left out, 31 December. Not used with a calendar, whose own years count.

Returns

Same type as the expression. The expression on the year's last date that the column holds.

Remarks

The year is the one of the last date in the filter context, ending on 31 December or on the year end given; its last date is the last one the column has in it.
With three arguments, a text in the third place is read as the year end, not as a filter: CLOSINGBALANCEYEAR ( [Close price], Prices[Date], "6-30" ).
When the expression is blank on that date, the result is BLANK.
Given a calendar instead of a column, it works on the calendar's own periods (fiscal years, retail months of four or five weeks, ISO weeks). A filter on a category the calendar does not define, such as a month on a calendar of weeks, can make the call an error.

Examples

The calendar year and a season to June

From the third quarter of 2025: the close on 31 December 2025, and on 30 June 2026 for a year that ends in June. The filter argument switches the commodity.

EVALUATE
CALCULATETABLE (
    ROW (
        "Year", CLOSINGBALANCEYEAR ( [Close price], Prices[Date] ),
        "Season", CLOSINGBALANCEYEAR ( [Close price], Prices[Date], "6-30" ),
        "Season, hops",
            CLOSINGBALANCEYEAR ( [Close price], Prices[Date], Prices[Commodity] = "Aroma hops", "6-30" )
    ),
    Prices[Commodity] = "Malting barley",
    DATESBETWEEN ( Prices[Date], DATE ( 2025, 7, 1 ), DATE ( 2025, 9, 30 ) )
)

Year	Season	Season, hops
5157.68	4763.15	253.41

OPENINGBALANCEYEAR, CLOSINGBALANCEQUARTER, ENDOFYEAR, TOTALYTD.

Last updated 3 November 2026 · OpenBI Studio 0.3.74