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

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

The ENDOFYEAR function

The ENDOFYEAR function is one of the time intelligence functions where it returns the end-of-year date.  It employs the following syntax:

Unlike its counterparts, the ENDOFMONTH function and the ENDOFQUARTER function, the ENDOFYEAR function contains two [2] arguments:

• dates: this is required which represents a column that contains dates.  This 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
• year_end_date: this is an optional, literal string that defines the year-end date.  If omitted, the default is 31 December.  It may be specified using =ENDOFYEAR(Calendar[Date], “30 June 1976”), for example.

It should be further noted that:

• this function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Please consider the following example where we have a Dates table with a date range from 1 May 2020 to 1 January 2025:

We can create a simple measure to get the end of the year date for each date in the Start Date column:

Then we can create a PivotTable to view our results:

All the dates return to the last date of the year for the respective year.  However, there is something odd in the PivotTable which is 1 January 2025 is supposed to return 31 December 2025 but in this case, it does not. 

Therefore, we should note the following when dealing with the time intelligence function:

• all dates need to be present for the years required.  All the days in this span, from January 1 to December 31, must be included in the Date table.  The date table must include all dates from commencement to the last day of a fiscal year if the report solely refers to fiscal years
• a column with a DateTime or Date data type and unique values is required.  Typically, this column is known as Date.  Although it is common practice, this is not necessary when defining associations with other tables.  However, the ‘mark as Date Table’ feature should relate to the Date column, which must have distinct values
• the Date table must be designated as a date table in the model in case the relationship between the Date table and any other table is not dependent on the Date.

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.