OPENINGBALANCEMONTH
Reads a value as the month opens: on the column's final date before the month begins.
Group: Time intelligence. Compatible with DAX.
Syntax
OPENINGBALANCEMONTH ( <expression>, <dates> [, <filter>] )| Parameter | Type | Description |
|---|---|---|
expression | Expression | The value wanted on that one date, usually a measure of a level such as a price, a stock or a balance. |
dates | Dates | A column of dates of the model, such as Sales[Month], or the name of a calendar defined in the model. |
filter | Filter optional | A condition or a table that filters as well, as a filter argument of CALCULATE does. |
Returns
Same type as the expression. The expression on the last date the column holds before the month.
Remarks
- The month is the one of the first date in the filter context. The opening is the closing of the month before:
CALCULATE ( expression, ENDOFMONTH ( PREVIOUSMONTH ( dates ) ) ). On business days, the previous month's last business day. - What trips report authors: grouped by another column of the dates' own table, such as
Prices[Month], that filter stays, the previous month's date falls outside it, and every opening is blank (the second example). Filter the dates column itself, or use a date table related to the facts on its date column. - When the expression is blank on that date, the result is BLANK.
- Given a calendar instead of a column, it works on the calendar's own periods (fiscal years, retail months of four or five weeks, ISO weeks). A filter on a category the calendar does not define, such as a month on a calendar of weeks, can make the call an error.
Examples
September's opening and closing price
The opening is the close of 31 August, the last business day before September.
EVALUATE
CALCULATETABLE (
ROW (
"Opening", OPENINGBALANCEMONTH ( [Close price], Prices[Date] ),
"Closing", CLOSINGBALANCEMONTH ( [Close price], Prices[Date] )
),
Prices[Commodity] = "Malting barley",
DATESBETWEEN ( Prices[Date], DATE ( 2026, 9, 1 ), DATE ( 2026, 9, 30 ) )
)| Opening | Closing |
|---|---|
| 4642.32 | 5149.75 |
By a month column of the same table: blank
Each row filters Prices[Month] too, and the day before the month is not in it.
EVALUATE
SUMMARIZECOLUMNS (
Prices[Month],
FILTER ( VALUES ( Prices[Month] ), Prices[Month] >= DATE ( 2026, 8, 1 ) ),
FILTER ( VALUES ( Prices[Commodity] ), Prices[Commodity] = "Malting barley" ),
"Closing", CLOSINGBALANCEMONTH ( [Close price], Prices[Date] ),
"Opening", OPENINGBALANCEMONTH ( [Close price], Prices[Date] )
)
ORDER BY Prices[Month]| Prices[Month] | Closing | Opening |
|---|---|---|
| 2026-08-01 | 4642.32 | (blank) |
| 2026-09-01 | 5149.75 | (blank) |
| 2026-10-01 | 5916.59 | (blank) |
Related functions
CLOSINGBALANCEMONTH, OPENINGBALANCEQUARTER, OPENINGBALANCEYEAR, PREVIOUSMONTH, ENDOFMONTH.