Downloads
DAXTime intelligence: balancesIntermediate

CLOSINGBALANCEMONTH

Evaluates an expression on the last date of the month in the filter context, for balances that do not add up over time.

Syntax
CLOSINGBALANCEMONTH(<expression>, <dates> [, <filter>])

Returns: A value (a scalar).

What it does

CLOSINGBALANCEMONTH returns the balance at the end of the month, not the total of the month's rows. Inventory, bank balances, receivables and headcount are balances: a stock count on March 31 is the stock on hand, not the sum of the stock counts taken each day. This function reads the value on the last day of the month, so the figure is right for the month-end close. It is the standard tool for semi-additive measures.

Syntax and arguments

Example

A manufacturer has a daily inventory snapshot with one row for each product on each day. The controller wants the units on hand at each month end.

Closing Units = CLOSINGBALANCEMONTH(SUM('Inventory'[Units]), 'Calendar'[Date])

In the March 2027 row, the measure returns the units on hand on March 31, 2027, for the products in that filter. Adding up the months would give a meaningless number, but the closing balance gives the count that appears on the balance sheet.

Example: a second, different use

The month-end cash balance for the operating bank account, from a daily balance table. The filter picks the account, and the function reads the balance on the last day.

Operating Cash Month End =
CLOSINGBALANCEMONTH(
    SUM('Bank_Balances'[Balance]),
    'Calendar'[Date],
    'Bank_Balances'[Account] = "Operating"
)

Things to watch

In business terms

CLOSINGBALANCEMONTH supports the month-end balance sheet for inventory, cash, receivables and deferred revenue. Controllers use it for the reconciliation of the bank and the stock count at close, and for the trend of balances across months. Getting balances right is the core of a semi-additive measure, and it is one of the most common errors in finance dashboards when the wrong sum is used.

Related functions

All DAX functions · View as Markdown · DAX fundamentals