The OPENINGBALANCEQUARTER function evaluates an expression at the beginning of the quarter in the current context.
OPENINGBALANCEQUARTER(
expression,
dates
[, filter]
)
- expression: Expression that returns a scalar.
- dates: Column containing dates.
- filter: Optional argument. Filter to apply to the current context.
The OPENINGBALANCEQUARTER 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 current context quarter, it is actually calculated for the end of the last day of the previous quarter. This means that the function will return the value Blank if there is no previous quarter for the current context (see example below).
If, given a table with sales, we define the measure
Ventas = SUM(FactSales[SalesAmount])
...calculating the total sales, and the measure:
Ventas comienzo de trimestre = OPENINGBALANCEQUARTER([Ventas], DimDate[Datekey])
...calculating the sales value with which a quarter begins, and we take these two measures to a matrix, the result is the following:
It can be seen how, for any day of the months of April and May, the measure "Ventas comienzo de trimestre" contains the sales figure with which the previous quarter was closed (corresponding to March 31). However, since the first quarter of 2007 is the first quarter for which data is available, the measure returns a Blank for any date in these months.
If, in the previous scenario, we put the month and year at the head of the rows, the result is the following:
Even though it cannot be appreciated correctly, the value of the measure "Ventas comienzo de trimestre" returns the value of sales for the last day of the last month of the previous quarter. The measure returns a Blank for the first three months of 2007 as it is the first quarter for which data is available.
