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

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 CLOSINGBALANCEYEAR.

The CLOSINGBALANCEYEAR function

This function evaluates the expression at the last date of the year 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.
• year_end_date: this is an optional literal string of the year-end date.  The default is December 31.  The year does not need to be included.

It should be noted that:

• year_end_date argument is a literal string of a date in the same locale as the locale where it was created.  The year portion is ignored
• 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

We have a Sales table below containing sales transactions in 2021.

Let’s create a measure named CBYear that returns the amount at the last date of the financial year ended in June. Depending on the locale settings, you might have MM/DD or DD/MM format.

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.