Daily DAX : Day 322 OPENINGBALANCEMONTH

Power BI DAX: OPENINGBALANCEMONTH Function

Description

The OPENINGBALANCEMONTH function in Power BI DAX returns the balance of a specified measure at the beginning of a given month, based on a date column and a filter context.

Syntax

OPENINGBALANCEMONTH(<expression>, <dates>[, <filter>])

• expression: The measure or calculation to evaluate (e.g., sum of sales).
• dates: A column containing date values, typically from a Date table.
• filter (optional): A filter expression to apply to the calculation.

Use Case

OPENINGBALANCEMONTH is used in financial reporting, inventory tracking, or any scenario requiring the starting balance of a measure at the beginning of a month. For example, it can calculate the opening balance of a bank account, inventory stock, or sales totals for a month.

Example

Suppose you have a table Sales with columns Date and Amount, and a related DateTable. To calculate the opening balance of sales for each month:

Opening Sales = OPENINGBALANCEMONTH(SUM(Sales[Amount]), DateTable[Date])

This measure returns the total sales amount at the start of each month, based on the DateTable[Date] column.

Notes

• Requires a proper date table marked as a Date table in Power BI.
• Works within the current filter context (e.g., slicers or filters applied in the report).
• Useful for time intelligence calculations like comparing opening and closing balances.