CLOSINGBALANCEMONTH Function (DAX)

How does the CLOSINGBALANCEMONTH function (DAX) work?

The CLOSINGBALANCEMONTH function (DAX) is a type of time intelligence function in DAX that evaluates the expression at the last date of the month using the current context.

Like all series functions, the CLOSINGBALANCEMONTH requires contiguous dates to work.

CLOSINGBALANCEMONTH Formula Syntax

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

How do you use the CLOSINGBALANCEMONTH function?

The CLOSINGBALANCEMONTH function is used to generate balance sheet values during instances where we want to view the inventory balance at the end of a period.

This function is not used in DirectQuery mode.

Related Blog Posts

Related Support Forum Posts

DAX Balance Sheet Include Closing Balance
Cashflow forecast – iterating results without circular ref
Cummulatives Total with time intelligence

Considerations when using the CLOSINGBALANCEMONTH function

The CLOSINGBALANCEMONTH has three parameters: the table expression that returns a scalar value, the column that contains dates, and the filter that applies to the current context.

Related Video Tutorials

Formula examples using the CLOSINGBALANCEMONTH function

=CLOSINGBALANCEMONTH(SUMX(ProductInventory,ProductInventory[UnitCost]*ProductInventory[UnitsBalance]),DateTime[DateKey])

= CLOSINGBALANCEMONTH ( SUMX (ProductInventory, [UnitsBalance]*[UnitCost]),ProductInventory[InventoryDate] )

=CLOSINGBALANCEMONTH(Sum([SalesAmount]), ‘DimDate'[FullDateAlternateKey], ALL(‘DimDate'[FullDateAlternateKey]))

Related DAX Functions

Time Intelligence Functions

Related Course Modules

Ultimate-Beginners-Guide-To-PowerBI

DOWNLOAD

To access our comprehensive DAX formula reference guide just insert your email address below. The resource will download immediately and we’ll also email it to you.