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

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

The EOMONTH function

The EOMONTH function is one of the date and time functions where it returns the date in DateTime format of the last day of the month before or after a specified number of months.  It employs the following syntax:

It has two [2] arguments:

• start_date: this is required and, represents the start date in DateTime format, or in an accepted text that represents a date
• months: this is required and, represents the number of months before or after the start_date.

Here are a few remarks about this function:

• DAX works with dates in DateTime format, so dates stored in other formats are converted in implicitly.  Whereas Microsoft Excel stored dates as a sequential serial number
•  if start_date is not a valid date, the EOMONTH function will return an error
• the EOMONTH function will change the value of the start date from a number to date if it is not in a DateTime format.  Before using the EOMONTH function, you should change the number's format to a DateTime one to prevent unexpected outcomes / errors
• the EOMONTH function gives an error if the start date plus the number of months results in an invalid date.  Dates after 31 December 9999 and before 1 March 1 are invalid
• if months is not an integer, it will be truncated
• the EOMONTH function utilises the client computer's locale and date time settings to interpret the text value when the date parameter is a text representation of the date to complete the conversion.  The string "1/8/2009" is regarded as a DateTime value corresponding to 8 January 2009 if the current date time settings indicate a date in the format of Month/Day/Year.  The same text would be regarded as a DateTime value corresponding to 1 August 2009 if the current date time settings indicate a date in the format of Day/Month/Year
• the ENDOFMONTH function is similar to the EOMONTH function when the months argument of the EOMONTH function is set to zero [0]
• this function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Please have a look at the following example we have the following Dates table ranging from 20 July 2020 to 9 August 2020:

We will write two [2] measures for the EOMONTH function and the ENDOFMONTH function to compare their differences:

EOMONTH Example

ENDOFMONTH Example

We will put these two measures in a PivotTable along with the Start Date and we will have the following visual:

the EOMONTH function return correct the end-of-the-month dates while the ENDOFMONTH function only returns the correct end-of-month dates for July.  This is because the ENDOFMONTH function is a time intelligence function hence it will be required a well-formed date table where all dates of the analyse period are present.  On the other hand, the EOMONTH function is one of the date and time functions therefore, it helps create calculations based solely upon dates and time.

Now, let’s have another example where we want to know what the last day of the month is after 36 months, using =EOMONTH(MAX(Dates[Start Date]),36):

Then, we will have the following PivotTable:

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.