Calculating Monthly Average Headcount for Employee Turnover Calculation

[PADDYN78]

Hello All,

I am very very new to PowerBI and I am in the process of setting up an HR Dashboard. To calculate the turnover on a monthly basis I am trying to calculate the average headcount for each month. the data in the table is set up as below

Employee No.	Commenced On	Terminated On
0354	1/04/2010	7/03/2022
0711	18/03/2014
0777	2/07/2014
0809	8/09/2014
01035	2/01/2019	4/12/2019
1061	14/08/2017
1062	14/08/2017	11/11/2019

I wrote the following measure to calculate monthly average headcount:

Average Monthly Headcount =
(
(
Var openinghc=
CALCULATE(COUNTROWS(FILTER(EMPTABLE),
(EMPTABLE[Commenced On]<= MIN(DATE_TABLE[MonthYear]))))
)

RETURN
(
var newjoiners=
CALCULATE(COUNTROWS(FILTER(EMPTABLE),
(EMPTABLE[Commenced On]<= MAX(DATE_TABLE[MonthYear])))))

RETURN
(
var terminations=
CALCULATE(COUNTROWS(FILTER(EMPTABLE,
(EMPTABLE[Terminated On]<=MAX(DATE_TABLE[MonthYear])))
)
)

RETURN
(
Var closinghc=
((openinghc+newjoiners)-terminations)

RETURN

CALCULATE(DIVIDE((openinghc+closinghc),2))
)
)
)
)
)

I dont get the right numbers using this solution. 

What am I doing wrong and How can i correct this?

[Ashish_Mathur]

Hi,

My approach would be to create one row for each month for each employee.  So for someone who joined in 2010 and resigned in 2013, then will be 12*13=156 rows.  This will result in a very large table.  Are you amenable to this approach?  If yes, then show the result of average employee headcount for any oen month.

[amitchandak]

@PADDYN78 , Refer the approch

Average of Rolling, Average of Snapshots: https://youtu.be/_pZRdLAJxxA

Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU