Total Years of Service of Employees in the department

[llchoong]

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.

[llchoong]

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.

[Nick_M]

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

[llchoong]

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

[Nick_M]

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

[llchoong]

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

[Nick_M]

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

[Nick_M]

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:

[llchoong]

Hi Nick, it worked. Thank you!