Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
2. I created 2 columns , one for today's date and another as follows
Solved! Go to 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.
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.
@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 Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |