OPENINGBALANCEYEAR

The OPENINGBALANCEYEAR function evaluates an expression at the beginning of the year in the current context.

Syntax

OPENINGBALANCEYEAR(
    expression,
    dates
    [, filter]
    [, year_end_date]
)

Parameters
  • 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).
Returned value

The OPENINGBALANCEYEAR function returns a scalar.

Additional Information

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.

Examples

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:

OPENINGBALANCEYEAR function. Example of use

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:

OPENINGBALANCEYEAR function. Example of use

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.

Category
Time intelligence
Submitted by admin on Tue, 12/04/2018 - 11:42