Forum Discussion
Headcount and Turnover based on Dataset
Hello
I am very new to Power BI and slowly learning it. I am doing some people metrics for my company. Unfortunately, the system we use only gives you the data as of date of creating the report (i.e. cannot retrive data as of a previous state of time).
My dataset looks like this (headers with * are caculuated on excel):
| Employee | Department | Business Unit | Employment Type | Status | Statistic Group | Employee Date | Termination Date | Grounds for Termination | Tenure in Months* | Tenure Group* |
| 103A59 | DEP3 | U1 | Part Time | Active | B | 28/07/2020 | null | null | 23 | 1-2 Years |
| 101C18 | DEP1 | U2 | Full Time | Terminated | A | 23/09/2013 | 6/07/2021 | RESIGN | 93 | 5-10 Years |
| 000B47 | DEP2 | U4 | Full Time | Active | A | 31/03/2003 | null | null | 231 | 15-20 Years |
| 102D13 | DEP1 | U2 | Casual | Terminated | B | 6/10/2015 | 10/06/2022 | REDUNDANT | 80 | 5-10 Years |
(The dataset is an appendum of Active Employees with Start Date before July 2022, and Terminated Employees from June 21 to June 22.)
I want to do a Column Chart of the Headcounts per Month End from June 21 to June 22 similiar to pictured below (fake numbers).
Because of the limitation of the dataset, to get an accurate headcount for month end, I will have to do this kind of calculation:
Headcount for Month End X =
("Sum of Active Employees" - "Employee Start Dates after X")
+
("Sum of Terminations" - "Termination Dates within Month X")
I can easily do this cacluation for each month on Excel. However, I've been asked to use the visualisation on Power BI with the ability to dynamically filter and splice (e.g. Department, Employee Type, Tenure). Is there a measure I have to use or calculated column? Also, how do I create date X Axis?
Futhurmore, from this, I will need to calculate Turnover Rate per Month. Again, I can do this on Excel, but I am struggling to do this on Power Bi.
Turnover Rate for Month = (
"Terminations within Month"
/
("Headcount From Pervious Month End" + "Headcount of Current Month End") /2)
)
x 100
I have to make sure they are dynamic for the purpose fo splicing and filtering.
I'm kind of lost on how to do this. I'm a very new to DAX.
Thank you in advance.
*edited, corrected turnover rate formula
Anonymous , Refer to the blog and attached file on this topic after signature
2 Replies
- amitchandak
Super User
Anonymous , Refer to the blog and attached file on this topic after signature
Hi
Thank you so much for this, I've got the headcount working after tweaking the formula. Added the brackets in Red.
From my underestanding and please correct me if I'm wrong, the original formula misses out a few lines as the || (OR) is expressed with the &&(And) rather from the Employee Start Date, so the added brackets will give me a more accurate number.
Current Employees = CALCULATE(COUNTx(FILTER(Employee,(Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date])) || Employee[End Date]>max('Date'[Date]))),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))
Thank you again, I just need to calculate the Turnover Rate now. I need the turnover rate per month as well as the overall rate from June 21- June 22, are you able to help me with that?
Turnover Rate for Month = (
"Terminations within Month"
/
("Headcount From Pervious Month End" + "Headcount of Current Month End") /2)
)
x 100Turnover Rate for Period = (
"Terminations all period"
/
("Headcount from the beginning of period [June-21]"+"Headcount at the end of the period [June-21]")/2)
)
x 100I have no idea what these actually calculate, can you explain sorry?
- Last Period Active Employees: Last Period Employees
- Period over Period Change %: Employee Change%
Thank you
*edited, corrected turnover rate formula