calculating month beginning and month end headcounts for turnover ratio

[Anonymous]

Hello, 

I have a date set like below and my objective is to calculate the month beginning and month-end headcounts for turnover ratio:

The formula is:

• on Jan 1st - month beginning, the headcount is 5 (A,B,C,D,E)
• on Jan 31st - month end, the headcount is 5 (A,B,C,D,F)
• so the average headcount is (5+5)/2 = 5
• during Jan, 1 person left (E)

so the turnover ratio is 1/5 = 0.2

My actual data has information for many months and I would like to show the monthly trends of the turnover ratio in a chart and in a table.

Thank you for all your help!

employee name	as of date	status	termination date	hire date
A	1/1/2020	Active		1/1/2019
B	1/1/2020	Active		1/1/2019
C	1/1/2020	Active		1/1/2019
D	1/1/2020	Active		1/1/2019
E	1/1/2020	Active		1/1/2019
A	1/31/2020	Active		1/1/2019
B	1/31/2020	Active		1/1/2019
C	1/31/2020	Active		1/1/2019
D	1/31/2020	Active		1/1/2019
E	1/31/2020	Terminated	1/20/2020	1/1/2019
F	1/31/2020	Active		1/15/2020

[amitchandak]

@Anonymous , Follow this blog. For the start of the month use MIN(Date[Date]). For the end of month use Max(Date[Date])

In the current employee formula https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970