Total Years of Service of Employees in the department


#1

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

YearsOfService = INT(YEARFRAC([DATEVALUE2], TODAY()))
where
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.


#2

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.


#3

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


#4

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