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.
hello
I am trying to work out how long the average duration of employment for a group of employees across a large company is over time
I have a table called Current_Worker_Detail_Report
This includes
Name
Cost Centre
HireDate
EndDate
For employees still working here, EndDate is blank.
Each employee is present only once in the dataset.
I have a Date table called Calendar
I have this measure which calculates the number of staff employed on any day since the dataset begins and seems to work fine:
StaffCount =var CurrentDate= max('Calendar'[DATE])
RETURN
CALCULATE(
DISTINCTCOUNT(Current_Worker_Detail_Report[Censhare Author Name]),Current_Worker_Detail_Report[Hire Date]<=CurrentDate, OR(ISBLANK(Current_Worker_Detail_Report[End date]),Current_Worker_Detail_Report[End date]>CurrentDate))
How do I go about calculating length of employment on an ongoing basis? DATEDIFF but then what I am using as EndDate as when I tried this with EndDate as Max(Calendar'Date'), the output over all time calculated the duration as ongoing until today (when the figure for Dec 2019 would be duration up to end of that month etc, and then January would be a month longer etc etc)
very grateful for any pointers
Hi @Anonymous ,
Could you please share your sample data and expected result to me if you don't have any Confidential Information? Please upload your files to OneDrive for Business and share the link here.
I can't upload it but this is the premise
Made up sample data
MadeUp Ltd launched on 1/1/2019 with ten staff. Therefore the average duration of employment in Feb 19 was 1 month.
Then half the MadeUp team decided to leave all at the same time at the end of the year (traitors!) and the company hired five new staff to replace them. So the average length of employment was 11 months in December 2019, but fell to 6 months in January 2020 (5 people had 12 months duration and 5 people had 0)
So this but for a dataset of 12,000 people
Name | Cost Centre | HireDate | EndDate |
Person 1 | Creative | 01/01/2019 | 01/01/2020 |
Person 2 | Creative | 01/01/2019 | 01/01/2020 |
Person 3 | Creative | 01/01/2019 | 01/01/2020 |
Person 4 | Creative | 01/01/2019 | 01/01/2020 |
Person 5 | Creative | 01/01/2019 | 01/01/2020 |
Person 6 | Creative | 01/01/2019 | |
Person 7 | Creative | 01/01/2019 | |
Person 8 | Creative | 01/01/2019 | |
Person 9 | Creative | 01/01/2019 | |
Person 10 | Creative | 01/01/2019 | |
Person 11 | Creative | 01/01/2020 | |
Person 12 | Creative | 01/01/2020 | |
Person 13 | Creative | 01/01/2020 | |
Person 14 | Creative | 01/01/2020 | |
Person 15 | Creative | 01/01/2020 |
Month | AverageEmploymentDurationMonths | AverageNumberofStaff |
Feb-19 | 1 | 10 |
Mar-19 | 2 | 10 |
Apr-19 | 3 | 10 |
May-19 | 4 | 10 |
Jun-19 | 5 | 10 |
Jul-19 | 6 | 10 |
Aug-19 | 7 | 10 |
Sep-19 | 8 | 10 |
Oct-19 | 9 | 10 |
Nov-19 | 10 | 10 |
Dec-19 | 11 | 10 |
Jan-20 | 5.5 | 10 |
Feb-20 | 7 | 10 |
Not sure I am following, generally, IF(ISBLANK([End Date]),TODAY(),[End Date]) or if you want till the end of the current month, IF(ISBLANK([End Date]),EOMONTH(TODAY(),0),End Date])
What I am hoping to get out is something like this - say we had lots of fairly longstanding staff suddenly leave in February and be replaced by brand new staff, the averageduration would decline in March
Date | AverageDurationOfEmployment |
01/01/2020 | 15 |
01/02/2020 | 16 |
01/03/2020 | 10 |
If I use TODAY as the EndDate, the average duration for previous time periods will be incorrect, I think? Because it would be calculating it to today, not until the end of the relevant month in the date column.
Does that make more sense?
thanks for your answer
@Anonymous
Refer, if My HR blog can help
some nice stuff there but nothing that seems to cover duration
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 |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |