The OPENINGBALANCEYEAR function evaluates an expression at the beginning of the year in the current context.
OPENINGBALANCEYEAR(
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 OPENINGBALANCEYEAR 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.
Although the function theoretically evaluates the expression passed as an argument for the first day of the year in the current context, it is actually calculated for the end of the last day of the previous year. This means that the function will return the value Blank if there is no previous year for the current context (see example below).
The year_end_date argument is a text string containing a date in the local computer format and allows you to specify an end of the year other than December 31st. Although this argument must include a year (for example, "10/21/2016"), only the day and month are considered.
If, given a table with sales, we define the measure
Ventas = SUM(FactSales[SalesAmount])
...calculating the total sales, and the measure:
Ventas comienzo de año = OPENINGBALANCEYEAR([Ventas], DimDate[Datekey])
...calculating the sales value with which a year begins, and we take these two measures to a matrix, the result is the following:
To confirm that the values returned by the "Ventas comienzo de año" measure correspond to the sales of the last day of the previous year, we take the days and the sales values for each of them to another matrix, and manually filter it to show only the information corresponding to the last day of each year:
We see how, indeed, the figures are as expected.
It can also be seen how, for the year 2007, the measure "Ventas comienzo de año" returns a Blank since there is no data for the previous year.
