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

  • Article

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:

Calculate Date Difference in Power BI

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:

Calculate Date Difference in Power BI

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

Thanks,
Sam Lester (MSFT)