The CLOSINGBALANCEMONTH function evaluates an expression for the last day of the month in the current context (or for the last day of the last month, if more than one is involved).
CLOSINGBALANCEMONTH(
expression,
dates
[,filter]
)
- expression: Expression that returns a scalar.
- dates: Column containing dates.
- filter: Optional argument. Filter to apply to the current context.
The CLOSINGBALANCEMONTH 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 month does not return a scalar (for example, if the last day of the month 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 mes = CLOSINGBALANCEMONTH([Ventas],DimDate[Datekey])
...defining the sales value with which a month ends, and we take these two measurements to a matrix, the result is the following:
It can be seen how, for any day in January, the measure "Ventas fin de mes" contains the sales figure for the last day of that month.
If, in the previous scenario, we put the month and year at the head of the rows, the result is the following:
Even when it cannot be appreciated, the value of the measure "Ventas fin de mes" returns the value of sales for the last day of the corresponding month.