Time-intelligence functions: CLOSINGBALANCEMONTH
Data Analysis Expressions (DAX) includes time intelligence functions to support the needs of Business Intelligence analysis by enabling you to manipulate data using time periods, including days, months, quarters, and years, and then build and compare calculations over those periods.
CLOSINGBALANCEMONTH : Evaluates the expression at the last date of the month in the current context.
The following sample formula creates a measure that calculates the ‘Month End Inventory Value’ of the product inventory.
To see how this works, create a PivotTable and add the fields, CalendarYear, MonthNumberOfYear and DayNumberOfMonth, to the Row Labels area of the PivotTable. Then add a measure, named Month End Inventory Value, using the following formula to the Values area of the PivotTable.
This DAX function is not supported for use in DirectQuery mode.
The dates argument can be any of the following:
- A reference to a date/time column.
- A table expression that returns a single column of date/time values.
- A Boolean expression that defines a single-column table of date/time values.