Hi all,
I need to calculate the total days of employment. Some employees have changed of employment agency and have therefore been provided a new employee_id. However I noticed that sometimes employee_id remains unchanged.
To calculate the days of employment I need your help on a measure. It should work like calculating the first startdate and last enddate, showing the total days so datediff( last enddate, first startdate, days)
Appreciate your help!
Somehow you’ll have to find some common item for employees that have changed contract or IDs. There’s no way around this.
What is the common information per actual employee? is it the name?
If you are looking to show this visually in an employee context really all you should have to do it find the min start date with MIN( StartDateColumn) and the for the max use MAX( EndDate)
Regardless if they have move the employee name will still pick up the start and end date within a measure and then all you have to do it minus one from the other.
Hi Sam,
It is a frustrating situation since there is “sometimes” involved.
Let’s say the the common information per actual employee is the name, and the employee context is applied. How would such a measure look? I have tried the following but it doesn’t seem to work.
Days of employment =
VAR StartDate = MIN(EmployeeDeployment[StartDate])
VAR EndDate = MAX(EmployeeDeployment[EndDate])
RETURN
CALCULATE( DATEDIFF(StartDate; EndDate; DAY))You should just need this below RETURN I would have thought.
EndDate - StartDate.
The CALCULATE function isn’t really doing much here.
If you place a formula in an employee context then the variable are doing all the work you need. You don’t need anything else I don’t believe.
Hi Sam,
Unfortunately it returns a strange answer. Any clue on what goes wrong?
Ok.
First make sure both those columns are type date not date and time.
Then also if you wrap the min and max formulas with VALUE( … ) this will turn them into a numeric number.
See how you go with this.