The CLOSINGBALANCEYEAR function evaluates an expression for the last day of the year in the current context (or for the last day of the last year, if more than one is involved).
CLOSINGBALANCEYEAR(
expression,
dates
[,filter]
[,year_end_date]
)
- expression: Expression that returns a scalar.
- dates: Column containing dates.
- filter: Optional argument. Filter to apply to the current context.
- year_end_date: Optional argument. Text string that defines the year-end date (the default is December 31).
The CLOSINGBALANCEYEAR function returns a scalar.
The dates argument can be a reference to a column containing dates, an expression that returns a table with a single column containing dates, or a Boolean expression that defines a table with a single column containing dates.
If you want to specify a year-end date, it is necessary to add the filter parameter which, ultimately, can be any filter that returns all the records involved in the analysis (such as ALLNOBLANKROW ).
The result of the function can be BLANK if the evaluation of the expression for the last day of the year does not return a scalar (for example, if the last day of the year in question does not exist in the date list).
If, given a table with sales, we define the measure
Ventas = SUM(FactSales[SalesAmount])
...with the total sales, and the measure
Ventas fin de año = CLOSINGBALANCEYEAR([Ventas],DimDate[Datekey])
...containing the sales value with which a year ends, and we take these two measurements to a matrix, the result is the following:
To confirm that the values returned by the measure "Ventas fin de año" correspond to the sales of the last day of the year, we take the days and the sales values for each of them to another matrix, and manually filter the table to display just the information corresponding to the last day of each year:
We see how, indeed, the figures are correct.
