The OPENINGBALANCEMONTH function evaluates an expression at the beginning of the month in the current context.
OPENINGBALANCEMONTH(
expression,
dates
[, filter]
)
- expression: Expression that returns a scalar.
- dates: Column containing dates.
- filter: Optional argument. Filter to apply to the current context.
The OPENINGBALANCEMONTH 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.
Even though the function theoretically evaluates the expression passed as an argument for the first day of the month in the current context, it is actually calculated for the end of the last day of the previous month. This means that the function will return the value Blank if there is no previous month 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 mes = OPENINGBALANCEMONTH([Ventas], DimDate[Datekey])
...calculating the sales value with which a month begins, and we take these two measurements to a matrix, the result is the following:
It can be seen how, for any day in February, the measure "Ventas comienzo de mes" contains the sales figure with which the previous month was closed. However, since January 2007 is the first month for which data is available, the measure returns a Blank for any date this month.
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 mes" returns the value of sales for the last day of the previous month. The measure returns a Blank for the month of January 2007 as it is the first for which data is available.
