Power Pivot Principles: The A to Z of DAX Functions – CLOSINGBALANCEMONTH

In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions.  This week, we look at CLOSINGBALANCEMONTH.

The CLOSINGBALANCEMONTH function

This function evaluates the expression at the last date of the month in a modified filter context.  Its syntax is as follows:

It has several arguments:

• expression: an expression that returns a scalar value
• dates: a column that contains dates
• filter: this is an optional Boolean expression or table expression that defines a filter.
• It should be noted that:
• 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
• this function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Example

Here, we have a Sales table below containing sales transactions in 2021:

Now, we will create a measure named CBMonth that returns the amount at the last date of every month.

The result is as follows:

Come back next week for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.