Calculating the difference between two dates in YEARS, MONTHS, and DAYS in Power BI or Excel

How do I calculate an employee’s tenure given their start date?

How can I determine a person’s age given their birth date?

In a customer conversation this week, I was asked to help solve this question in Power BI. Interestingly, I was intrigued by this topic almost 5 years ago when I wrote a blog entry to solve this in TSQL, but now it is time to solve it in DAX for use in Excel or Power BI. The challenge with this question is that it sounds so simple, yet turns out to be a bit tricky. There are likely several other creative ways to solve this. If you have found another way, please share it in the comments below.

Let’s start with a list of employees and their start dates:

Now create a measure to represent today’s date (or whatever date you want to use as the end date for the calculation).

TodaysDate = FORMAT(TODAY(),"mm/dd/yyyy")

I am using YEARFRAC as the basis of the year/month/day calculations. YEARFRAC returns the number of years in a fractional format between two dates. From this value, we can use additional math functions, such as FLOOR() and MOD() to break out the individual year/month/day components.

Create the following columns in Power BI (or formulas in Excel):

Years = FLOOR(YEARFRAC(Source[StartDate],[TodaysDate]),1)

Months = FLOOR(MOD(YEARFRAC(Source[StartDate],[TodaysDate]),1) * 12.0,1)

Days = SWITCH(DAY(Source[StartDate]) > DAY([TodaysDate]),
TRUE(), (DAY(EOMONTH([TodaysDate],-1)) - DAY(Source[StartDate])) + (DAY([TodaysDate])),
FALSE(), (DAY([TodaysDate])-DAY(Source[StartDate])))

Using these calculations, we can display the date difference in years, months, and days:

The sample .pbix file can be downloaded here to explore on your own.

Thanks,
Sam Lester (MSFT)

Comments

• Anonymous
  April 06, 2017
  The comment has been removed
  • Anonymous
    April 06, 2017
    Hi Marek, great job on your presentation at CeBIT. The crux of the problem is that DATEDIFF seems like the logical function, but it calculates in DAX differently than SQL Server and other languages that I'm more experienced with. It returns "the count of interval boundaries crossed between two dates", so DATEDIFF(12-31-2016, 1-1-2017, year) returns 1 even though this person would have only been employed for a single day. Since it "crossed the year boundary", the value of 1 is returned. Because of the way this functions compared to the specific request from the customer to return year/month/days of employment, I needed a different solution other than DATEDIFF, which is why I switched to YEARFRAC.
    • Anonymous
      February 19, 2018
      Cann’t we use Datedif(old date, Today, “Y”) for Year diff.Datedif(old date, Today, “YM”) for remaining months after years and Datedif( old date, Today, “MD”) for remaining days?
• Anonymous
  April 07, 2017
  Hi Sam,real nice DAX-code!Here comes my M-version for the use in the query editor (PowerBI) or PowerQuery in Excel. It is a record, that you define within a newly created column (so make sure you include the square brackets). Simply just expand Years, Months and Days from it at the end:[Today = Date.From(DateTime.LocalNow()),BirthdayFactor = if Date.DayOfYear([StartDate])<Date.DayOfYear(Today) then 0 else 1,DayFactor = if Date.Day(Today)<Date.Day([StartDate]) then -1 else 0,Years = Date.Year(Today)- Date.Year([StartDate]) - BirthdayFactor,Months = Date.Month(Today)-Date.Month([StartDate]) + 12*BirthdayFactor + DayFactor,Days = Number.From(Today)-Number.From(#date(Date.Year(Today), Date.Month(Today) + DayFactor, Date.Day([StartDate]))) ]Because we don't have the useful YEARFRAC-function in M, I'm using 2 helper-factors to prevent ugly nested if-statements.
  • Anonymous
    April 10, 2017
    Hi Imke, thanks for including the M code. I was just asked today about M vs. DAX and came back to this post to show the difference between the two, including your M code, where to access them in Power BI, etc. Sam
• Anonymous
  May 20, 2018
  Mentioned Days DAX formula .. Can we use in "New Measure" ??
• Anonymous
  June 20, 2018
  Thanks for sharing, this looks great. I believe this only works for single values. How would we create one for data that has multiple values that need to be calculated?
• Anonymous
  July 11, 2018
  Love it thanks!
• Anonymous
  September 17, 2018
  The comment has been removed
• Anonymous
  December 19, 2018
  The comment has been removed