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 ()
