Forum Discussion
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...
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 _daysRegards,
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.
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 , thanks for your reply but I am getting the same result
nadir Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.