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:

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)

 

Comments (4)

  1. Hi Sam, how about using DATEDIFF Function: https://msdn.microsoft.com/en-us/library/dn802538.aspx?f=255&MSPPError=-2147217396
    It spares couple line codes and is very generic 😉

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

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

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

Skip to main content