Total Years of Service of Employees in the department

In a stats dashboard I am working on getting the Total accumulated number years of service from ALL 38 ACTIVE employees in the department. I am given Employee Names and each of their Original Hire Date. After trying out DATEDIFF numerous times that did not work for me, I finally succeeded with

DATEVALUE2 = DATEVALUE(min('Table'[Orig Hire Date]))

My question is how do I get the TOTAL calculated YearsOfService from all 40 Employees? I have tried CALCULATE([YearsOfService], ALL('Diversity Dashboard'[Name])) but the answer keeps giving me 36, which is the longest years of service of one of the employees.

If you can also provide a DAX using DATEDIFF that is appreciated also.
Datediff = DATEDIFF(MIN('Table'[Orig Hire Date]), TODAY(),YEAR) gave me a total sum of 37 which is longest service years.

Can you help? Thank you.

After I posted the above, I created the following columns which works close (for now)

1. Orig Hire Date 1 = DATEVALUE('Table'[Orig Hire Date])
2. Today = Today()
3. Calculated Measure Diff2 = [Today].[Year] - [Orig Hire Date 1].[Year]

The above works BUT Sum of DIFF2 gives additional years of service because it calculates those less than a year of service as 1. The correct answer should be 305 but Sum DIFF2 gives 339.

Any ideas of refinement from the above DAX is appreciated.

This type of thing is much easier and faster using M and Power Query vs. DAX. Can you add some sample data?


Hi Nick, did you get the sample data emailed to you?

I did not. Probably best to just add it here.


Hi Nick, here is the file. Let me know how you would calculate the years of service based on the department start date. It seems similar to calculating birthdays but I cannot seem to get the write calculations based on what I googled.YearsofService

I think you may have attached the wrong file. This one is “Quantity Shipped Last 6 Months”


Let’s see if this can help:
After loading the table into Power Query:

  1. Created a Custom Column that will give the # of days since the original hire date to today’s date:
- [Original Hire Date]

Then Added another column, which is 365/ Days Since Hire
which gives you this table:


Hi Nick, it worked. Thank you!