Forum Discussion

nadir's avatar
nadir
Frequent Visitor
5 years ago
Solved

Issue with Datediff Function

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
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    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.

5 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    5 years ago

    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's avatar
    amitchandak
    Super User
    5 years ago

    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

      • amitchandak's avatar
        amitchandak
        Super User
        5 years ago

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