I am trying to calculate the number of employees at the beginning and the end of each month. I have a calculation for the beginning of the month (below) but can't figure out how to convert that to the end of the month count.
Beginning of month calculation :
IIF([Employee Status]="Active",DATEPART('month',TODAY()),0)
This does give me the numbers I need. Any help is appreciated.
@Cheryl Mandall hi there,
I cannot see here the date scaffolded.
It should be the months/year/start month and end month.
Anyway, let's get to the bottom of this.
To make it possible let's change the empty End Date to "9999-01-01"- means the will be active till year 9999 unless the termination is changed to a early one. This is just to replace the blanks and make it useful.
I created the months for scaffold date (tried to hit the best practices) for 2021/2022 year (you can extend).
Now we join them together.
If the Start Date <= End of the month - Active user at least till the end of the month.
If the End date >= Start date of the month means the user is not year terminated.
You can test here and experiment and adapt to your data if this is not working.
Now you can see you data multiplied for the rows which make sense for each period.
Let's get into calculations.
// #New hire
COUNTD(IF [Start Date]>=[Start Month] and [Start Date]<=[End Month] THEN [Employee Number] END)
If the Start date is in between that month it'll count there as a New Hire.
Same for the termination:
// ⌗Terminations
COUNTD(IF [End Date]>=[Start Month] and [End Date]<=[End Month] THEN [Employee Number] END)
⌗Active (headcount)
COUNTD(IF [Employee Status]="Active" and [End Date]>[End Month] THEN [Employee Number] END)
Active - termination outside of the period and status Active.
Now you can plot the data.
Just because you're going to stick with the Current Year or Previous Year I'll continue adding calculations.
Example for the Termination CY:
COUNTD(IF [End Date]>=[Start Month] and [End Date]<=[End Month] and [Year]=2022 THEN [Employee Number] END)
Same for the rest of the indicators, you only need to create the PY calculations.
When calculating the turnover is it getting a little more tricky.
You need to calculate cumulative numbers for the Terminations and the average Active users. (hope this is the calculation).
Same as CY but cumulative, and it should be uses as Running total.
{ FIXED [Month], [Year]: COUNTD(IF [Year]=2022 and [Month]<={ FIXED [Month], [Year]:MAX([Month])}
and [End Date]>=[Start Month] and [End Date]<=[End Month]
THEN [Employee Number] END)}
For the Active users same, just we need to use the Running Average.
{ FIXED [Month], [Year]: COUNTD(IF [Year]=2022 and [Month]<={ FIXED [Month], [Year]:MAX([Month])}
and [Employee Status]="Active" and [End Date]>[End Month]
THEN [Employee Number] END)}
And this is the result in the view (cumulative terminations and average active users).
I left "Sheet1" for you to see the table calculations.
Final calculation for the Turnover %:
RUNNING_SUM(SUM([cumulative terminations]))
/
WINDOW_AVG(SUM([cumulative Active]), -2, 0)
Now we can plot it. Also I create 2 views and added the Termination with minus (so you can check In and Out).
So you can check the active employees for the CY and employee turnover %, with New Hires and terminations.
Next steps it's up to you. You can create parameters for the CY and PY years and replace 2022 and 2021 numbers.
Create the rest of PY calculations -> so you can compare with previous year.
Document attached.
Let me know,
Adrian
