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

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

The EDATE function

The EDATE function is one of date and time functions and, it returns the date that is the indicated number of months before or after the start date.  It employs the following syntax:

It has two [2] arguments:

• start_date: this is required and represents the date in datetime or text format that represents the start date
• months: this is also required and represents an integer to denote the number of months before or after the start_date.

The following should be noted:

• DAX works with dates in datetime format, so dates stored in other formats are converts implicitly, whereas Microsoft Excel stores said dates as a sequential serial number instead
• if start_date is not a valid date, the EDATE function will return an error
• if months is not an integer, it will be truncated
• the EDATE 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 January 8, 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 August 1, 2009 if the current date time settings indicate a date in the format of Day/Month/Year
• the final day of the relevant month is returned if the desired date falls after that day.  For instance, the return date of February 28, 2009 from the following functions: EDATE("2009-01-29", 1), EDATE("2009-01-30", 1) and EDATE("2009-01-31", 1), is one month after the start date
• this function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Consider the following example:

The follow DAX code is used to add 9 months to 17 January 2022 which results in the following table:

Besides adding months to the start_date we may subtract months as well:

This will return the following table:

Let’s have one example using a DAX measure with the following Dates table:

Now we can create a measure to add one [1] month to the Start Date column with the following DAX code:

Here we receive an error message here since we need to specify an aggregation here to get a single result so, we must modify the DAX code as follows:

After we put this into the Pivot Table, we will obtain the following visual:

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.