Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello, I have put a lot of time into this but dont feel I have an accurate number on what I need.
I am trying to take the hire date and termination date of an employee and count that, which I think I have. I also need to then take the number of terminations and divide by the number of average staff working from a given time period. I am hoping I can do this through the filter. There are employees who are no longer working for the company, but they woudl be counted in months/years in the past when they were working as historical data. So basically what it needs to do it say something like look for employees who have start date dates before the month being filtered and terminations dates after the period and count as one. take those numbers for each previous month/year. Then Divide number of terminations within that month/year and divide by the active count for that specific month or year. Better yet would be divide numbers of terminations that month/active begining of month or year +average count end of month/year divide by two
the equation for turnover is this:
Turnover = divide(terminations,average(activecount)
But with looking into the past for specific numbers, its been difficult to put it all in a graph with historical data as this will always show the current active staff and not staff from the past.
Some things I tried but was not successful in:
Turnover Rate % = DIVIDE(COUNT('Paycom EmployeeDemographics'[TerminationDate]),CALCULATE(SUM('Paycom EmployeeDemographics'[New Employee Count C]),DATESBETWEEN('calendar'[Day_Date],MIN('calendar'[Day_Date]),MAX('calendar'[Day_Date]))))
Turnover Rate % = DIVIDE(COUNT('Paycom EmployeeDemographics'[TerminationDate]),CALCULATE(SUM('Paycom EmployeeDemographics'[New Employee Count C]),PARALLELPERIOD('calendar'[Day_Date],1,YEAR)))
Turnover Rate % = DIVIDE(COUNT('Paycom EmployeeDemographics'[TerminationDate]),CALCULATE(SUM('Paycom EmployeeDemographics'[New Employee Count C]),SAMEPERIODLASTYEAR('calendar'[Day_Date])))
Turnover Rate % = DIVIDE(COUNT('Paycom EmployeeDemographics'[TerminationDate]),CALCULATE(SUM('Paycom EmployeeDemographics'[New Employee Count C]),DATESINPERIOD('calendar'[Day_Date],MAX('calendar'[Day_Date]),-1,YEAR)))
Tried this one to give me an active count, it seemed to work, but I am not able to add it into the turnover equation and have it work correctly. I thought maybe I needed to add the allexcept funtion, but I am not sure where to place it within this function to get it to work.
VAR CurrentDate = MAX ( 'calendar'[Day_Date] )
RETURN
SUMX (
'Paycom EmployeeDemographics',
IF (
'Paycom EmployeeDemographics'[HireDate] <= CurrentDate
&& OR ('Paycom EmployeeDemographics'[TerminationDate] >= CurrentDate , ISBLANK('Paycom EmployeeDemographics'[TerminationDate]) ),
1,
BLANK ()
)
)
Tried incorporating it into a total equation but it was not working correctly
Employee Turnover % = CALCULATE(DIVIDE([# of Terminations],SAMEPERIODLASTYEAR('calendar'[Day_Date]SUMX (
'Paycom EmployeeDemographics',
IF (
'Paycom EmployeeDemographics'[HireDate] <= MAX('calendar'[Day_Date])
&& ('Paycom EmployeeDemographics'[TerminationDate] >= MAX('calendar'[Day_Date] , ISBLANK('Paycom EmployeeDemographics'[TerminationDate]))
1,
BLANK ()
Solved! Go to Solution.
Hi @ShawnG2G2
Lack of sample data to make a judgment.
There is a blog in the forum related to this, please check if it is useful.
HR Analytics - Active Employee, Hire and Termination trend
Or would you please provide a sample file for testing, it would be very helpful, thanks in advance.
How to provide sample data in the Power BI Forum
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
Just adding to this, I tried this out but not sure its accurate
Hi @ShawnG2G2
Lack of sample data to make a judgment.
There is a blog in the forum related to this, please check if it is useful.
HR Analytics - Active Employee, Hire and Termination trend
Or would you please provide a sample file for testing, it would be very helpful, thanks in advance.
How to provide sample data in the Power BI Forum
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
13 |
User | Count |
---|---|
91 | |
85 | |
46 | |
28 | |
21 |