Assistance with Turnover rate calculation

[PBI5851]

I am working on trying to capture the turnover rate but for some odd reason, i keep getting the wrong numbers especially if i select a filter. In my measure i am using the ALL constraint, but it doesnt seem to apply for "employee count at beginning" and employee count at end". 

The formula i am using is

Employee who left during the period / ((employee at beginning of time period + employee at end of time period) /2)

Please can you advise what am i missing. 

Employee_Code	HireDate	Termination_Date	Termination_Reason
E101	8/7/2019	11/11/2021	Moved
E102	10/21/2019	6/16/2020	Better Offer
E103	2/3/2020	4/30/2021	Resigned
E104	2/8/2021	null
E105	3/8/2021	6/8/2021	Moved
E106	8/23/2021	9/30/2021	Better Offer
E107	10/18/2021	11/8/2021	Resigned
E108	2/28/2022	null
E109	9/15/2019	1/1/2021	consultant
E110	1/1/2017	null
E111	11/1/2017	3/24/2021	resignation
E112	6/29/2020	null
E113	1/1/2017	9/22/2021	Covid vaccine policy
E114	8/26/2019	null
E115	8/27/2012	12/24/2013	Moved
E116	8/27/2002	2/12/2013	Better Offer
E117	8/27/2012	1/1/2013	Resigned
E118	8/27/2012	1/31/2017
E119	8/27/2012	4/18/2014
E120	9/4/2012	2/18/2013
E121	8/27/2012	1/1/2013
E122	9/4/2012	7/5/2013
E123	9/4/2012	7/16/2013

Employee Count at Beginning (turnover) =

CALCULATE(COUNTROWS(EmpTable),ALL(EmpTable),

FILTER(VALUES(EmpTable[HireDate]), EmpTable[HireDate] <= MIN('Date Table'[Date])),

FILTER(VALUES(EmpTable[Termination_Date]), OR (EmpTable[Termination_Date] >= min('Date Table'[Date]), ISBLANK(EmpTable[Termination_Date]))))

Employee Count at End (turnover) =

CALCULATE(COUNTROWS(EmpTable),ALL(EmpTable),

FILTER(VALUES(EmpTable[HireDate]), EmpTable[HireDate] <= max('Date Table'[Date]) ),

FILTER(VALUES(EmpTable[Termination_Date]), OR (EmpTable[Termination_Date] >= max('Date Table'[Date]), ISBLANK(EmpTable[Termination_Date]) ) ) )

Employees who left (turnover) =

CALCULATE(COUNTROWS(EmpTable),

FILTER(VALUES(EmpTable[Termination_Date]),

AND (EmpTable[Termination_Date] >= min('Date Table'[Date]), EmpTable[Termination_Date] <= max('Date Table'[Date]))))

Employee Turnover (Denominator) = (EmpTable[Employee Count at Beginning (turnover)] + EmpTable[Employee Count at End (turnover)]) / 2

Employee Annual Turnover Rate = DIVIDE(EmpTable[Employees who left (turnover)] , [Employee Turnover (Denominator)], 0)

[PBI5851]

@ChrisMendoza - thank you for the solution. i am trying that out, but I noticed that if i have a slicer on the termination reason, the "beginning" and "end counts" are way off, inspite of using the ALL function. Do you notice the same behavior too ? How to resolve such an issue. 

[ChrisMendoza]

@PBI5851 - I really wanted to get this working once I started tinkering with it. I took a look at Power BI: Employee turnover rate template - Finance BI (finance-bi.com) but I don't think I ended with the correct results. Here's what I managed to work through; hopefully you might get another idea on how to tackle this:

Inactive on [Termination_Date] ➡️ [Date]

Employee Count = 
VAR __SelectedDate = MAX('Date Table'[Date])
RETURN
    SUMX(
        EmpTable,
        VAR __startDate = [HireDate]
        VAR __termDate = EmpTable[Termination_Date]
        RETURN 
            IF(
                __startDate <= __SelectedDate &&
                OR(__termDate >= __SelectedDate, __termDate = BLANK()),
                1,
                0
            )
    )

Employee Count Running Total = 
VAR __maxDate = MAX('Date Table'[Date])
RETURN
    CALCULATE(
        [Employee Count],
        'Date Table'[Date] <= __maxDate,
        ALL('Date Table')
    )

Employees who left = 
IF(
    NOT(ISBLANK([Employee Count Running Total])),
    CALCULATE(
        [Total Count],
        USERELATIONSHIP(EmpTable[Termination_Date],'Date Table'[Date])
    )
)

Turnover = DIVIDE([Employees who left],(([Employees at the beginning] + [Employee Count Running Total]) / 2))