Issue with Datediff Function

[nadir]

Hi,

I have an Employee table which has ID,name,Department,Hire date etc.. , I want to calculate total Service period for Employees in Years , Months , Days by substracting Hire date from Today every time the report is opened , I used two methods 
1. I created A measure as follows

Service Period in Years = DATEDIFF('pp EMPLOYEES'[HIRE_DATE_G],TODAY(),YEAR)

2. I created 2 columns , one for today's date and another  as follows

Service in Years in Column = DATEDIFF('pp EMPLOYEES'[HIRE_DATE_G],'pp EMPLOYEES'[Todays date],YEAR)
Both methods are giving me a wrong result , for example the hire date for Employee is 17/03/2012 and today's date is 13/01/2021 , I am getting 9 Years 10 Months 14 Days where the correct result must be 8 Years 9 Months 27 days
I don't know what is wrong , I checked the date format , I tried a measure , I tried a column but no luck.

Please help
Nadir

[v-jingzhang · Accepted solution]

Hi @nadir 

Please try the following measures. I didn't test them with many instances, so feel free to let me know if they don't work in some conditions.

Today = TODAY()

Years = INT(YEARFRAC(SELECTEDVALUE('Table'[HIRE_DATE]),[Today],1))

Months = INT(MOD(YEARFRAC(SELECTEDVALUE('Table'[HIRE_DATE]),[Today],1),1)*12)

Days =
VAR _HireDate = SELECTEDVALUE ( 'Table'[HIRE_DATE] )
VAR _Today = [Today]
VAR _LastMonthDays = DAY ( EOMONTH ( [Today], -1 ) )
VAR _days =
    SWITCH (
        TRUE (),
        DAY ( _HireDate ) <= DAY ( _Today ), DAY ( _Today ) - DAY ( _HireDate ),
        DAY ( _HireDate ) > DAY ( _Today ), DAY ( _Today ) + ( _LastMonthDays - DAY ( _HireDate ) )
    )
RETURN
    _days

Regards,
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

[amitchandak]

@nadir , In this case, better to try a column like

Service in Years in Column = DATEDIFF('pp EMPLOYEES'[HIRE_DATE_G],'pp EMPLOYEES'[Todays date],Month)/12.0

check if this works better

[nadir]

@amitchandak  , thanks for your reply but I am getting the same result

[amitchandak]

@nadir Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[nadir]