The CLOSINGBALANCEQUARTER function evaluates an expression for the last day of the current context quarter (or for the last day of the last quarter, if more than one is involved).
CLOSINGBALANCEQUARTER(
expression,
dates
[,filter]
)
- expression: Expression that returns a scalar.
- dates: Column containing dates.
- filter: Optional argument. Filter to apply to the current context.
The CLOSINGBALANCEQUARTER 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.
The result of the function can be BLANK if the evaluation of the expression for the last day of the quarter does not return a scalar (for example, if the last day of the quarter in question does not exist in the date list).
If, given a table with sales, we define the measure
Ventas = SUM(FactSales[SalesAmount])
...defining the total sales, and the measure
Ventas fin de trimestre = CLOSINGBALANCEQUARTER([Ventas],DimDate[Datekey])
...defining the sales value with which a quarter ends, and we take these two measurements to a matrix, the result is the following:
It can be seen how, for any day in the months of August and September, the measure "Ventas fin de trimestre" contains the sales figure for the last day of said quarter (September 30).
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, the value of the measure "Ventas fin de trimestre" returns the value of sales for the last day of the last month of the corresponding quarter.
