Calculate Employee Length of Service with company

[yaman123]

Hi all, 

I have the below measure but it isnt producing the correct results. 

I would like to show the length of service an employee has been with the business. The below measure isnt producing the correct outcome. If an employee is still with the business and started on 03/12/2018, this is showing as 1 year instead of 2 years. If an employee hasnt started with the company yet but a record exists, this is showing as -1 whereas i would like to show this as 0. 

I will then need to show the average length of service by gender once the measure is correct. 

I have a date table which i select the month year to run the data for that month and an employee table with employee name, start date, end date etc.. 

Employee LOS =

IF(ISBLANK(MAX(Query1[DATE_OF_LEAVING])),DATEDIFF(MAX(Query1[DATE_OF_EMPLOYMENT]),MAX('Date'[Date]),YEAR),DATEDIFF(MAX(Query1[DATE_OF_EMPLOYMENT]),MAX(Query1[DATE_OF_LEAVING]),YEAR))

Thanks

Yasir

[Chandra_maha]

Hi, you can use following mesaure to calulate it
DateTenure as of Date Chosen =
VAR DateChoose =
MAX ( Dates[Date] )
RETURN
IF (
MAX ( 'Table'[Last Hire Date] ) > DateChoose,
"#NUM!",
DATEDIFF (
MAX ( 'Table'[Last Hire Date] ),
MIN ( COALESCE ( MAX ( 'Table'[Termination Date] ), TODAY () ), DateChoose ),
DAY
) / 365
)

[v-kelly-msft]

Hi @yaman123 ,

I would make some suggestions as following:

1.You need to create a calculated column first to check whether the employee has started business;

2.Modify your measure as below:

Employee LOS =
IF(ISBLANK(MAX(Query1[DATE_OF_LEAVING]))&&MAX(Query1[STARTED BUSINESS])<>0,DATEDIFF(MAX(Query1[DATE_OF_EMPLOYMENT]),TODAY()),YEAR),DATEDIFF(MAX(Query1[DATE_OF_EMPLOYMENT]),MAX(Query1[DATE_OF_LEAVING]),YEAR))

If the above measure doesnt work,would you pls provide some sample data for testing?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!