## 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)

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 😉

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.

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?

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.

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

Mentioned Days DAX formula .. Can we use in “New Measure” ??

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?

Love it thanks!

Hello Sam thsi is brilliant but there seems to be an issue with a few dates that I am struggling to understand…

I have broken down “(DAY(EOMONTH([TodaysDate],-1)) – DAY(Source[StartDate])) + (DAY([TodaysDate]))” into excel to see what is happening because I get a -1 for the start date 30/10/2006 with end date 1/03/2017 as follows:

B C D E F G H

StartDate EndDate DAY(EOMONTH(Endate,X)) Day(StartDate) DAY(EndDate) D2-E2+F2 Result

3 39020 42795 =DAY(EOMONTH(C3,-1)) =DAY(B3) =DAY(C3) =D3-E3+F3 -1

4 39020 42795 =DAY(EOMONTH(C4,0)) =DAY(B4) =DAY(C4) =D4-E4+F4 2

5 39020 42795 =DAY(EOMONTH(C5,1)) =DAY(B5) =DAY(C5) =D5-E5+F5 1

I seem to get negative results with Start Date 30/04/04 and enddate 1/2/17 when using DAY(EOMONTH(C5,0))

Could you help out please … It just seems that you can get a negative result for Day part of the equation…

Hi everyone, the Dax code is great. I would like to see this work in PQ. I did try Imke’s code, but it did not quite fit the bill.

What I would like to work out is the complete years/months/residual days between 2 dates. Thus the difference between 2 same dates is 1.

I have been able to do this within XL quite easily but am struggling with PQ. Examples: (dates are in YY MM DD format). The calendar days difference are not necessary to what is needed!

From To Years Months Days Calendar days

1/02/2018 28/02/2018 0 1 0 28

1/02/2018 1/03/2018 0 1 1 29

1/02/2004 29/02/2004 0 1 0 29

1/02/2004 1/03/2004 0 1 1 30

2/02/2004 1/03/2004 0 1 0 29

2/02/2018 1/03/2018 0 1 0 28

1/03/2017 31/03/2017 0 1 0 31

1/03/2017 1/04/2017 0 1 1 32

15/03/2017 14/04/2017 0 1 0 31

15/03/2017 21/08/2017 0 5 7 160

21/08/2017 20/08/2018 1 0 0 365

21/08/2017 22/08/2018 1 0 2 367

21/08/2017 31/12/2018 1 4 11 498

Totals 4 6 23 1657

Formula for Years: =DATEDIF(A2,B2+1,”y”)

Formula for Months:=DATEDIF(A2,B2+1,”ym”)

Formula for Days:=IF(ISBLANK(A2),,DATEDIF(A2,B2+1,”md”))

Formula for Calendar days:=IF(A2=0,0,B2-A2+1)

Totals:

Years=INT((INT(SUM(E2:E16)/30)+SUM(D2:D16))/12)+SUM(C2:C16)

Months=MOD(INT(SUM(E2:E16)/30)+SUM(D2:D16),12)

Days=MOD(SUM(E2:E16),30)

Calendar days = Sum!

Any suggestions would be appreciated. I tried splitting the dates into separate components to sort of replicate the above however was not successful.

I can send a file of the above if needs be!

Thanks

Sylvain