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
Center of Excellence

DOWNLOAD


Download our comprehensive DAX Formula Reference Guide as perfect companion as you learn how to use DAX formulas within Power BI.


Download DAX Formulas Reference Guide
Download DAX Formula Reference Guide
Membership