OPENINGBALANCEQUARTER

The OPENINGBALANCEQUARTER function evaluates an expression at the beginning of the quarter in the current context.

Syntax

OPENINGBALANCEQUARTER(
    expression,
    dates
    [, filter]
)

Parameters
  • expression: Expression that returns a scalar.
  • dates: Column containing dates.
  • filter: Optional argument. Filter to apply to the current context.
Returned value

The OPENINGBALANCEQUARTER 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 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).

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 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:

OPENINGBALANCEQUARTER function. Example of use

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:

OPENINGBALANCEQUARTER function. Example of use

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.

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