Headcount and Turnover based on Dataset

[Anonymous]

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

[amitchandak · Accepted solution]

@Anonymous , Refer to the blog and attached file on this topic after signature

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[Anonymous]

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 100

Turnover 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 100

I have no idea what these actually calculate, can you explain sorry?

1. Last Period Active Employees: Last Period Employees
2. Period over Period Change %: Employee Change%

Thank you

*edited, corrected turnover rate formula