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.