Chat with us, powered by LiveChat

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.

Example

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.

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

This DAX function is not supported for use in DirectQuery mode.

Remarks

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.

READ: Time-Intelligent Functions – CLOSINGBALANCEQUARTER

Share with your followers...
Email this to someone
email
Share on Facebook
Facebook
Tweet about this on Twitter
Twitter
Share on LinkedIn
Linkedin

Post Author: David K. Kandie