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?

Enterprise%20DNA%20Expert%20-%20Small


#4

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


#5

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

Enterprise%20DNA%20Expert%20-%20Small


#6

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


#7

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

Enterprise%20DNA%20Expert%20-%20Small


#8

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:
Duration.Days(
     DateTime.Date(
            DateTime.LocalNow()
   ) 
- [Original Hire Date]
 )

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

Enterprise%20DNA%20Expert%20-%20Small


#9

Hi Nick, it worked. Thank you!