Calculating Employee turnover rate per month

[MDeemter]

Hi guys,

Eventhough I have read several posts on this topic, non seem to answer the question(s) that I have, so here I go:

I am trying to calculate the monthly turonver rate of my employees. 
The tables and columns present are:

1. A datetable

2. An employee table with:

- Employee ID
- Starting Date

- Termination Date

Preferably I would like the turnover rate to be calculated as:
Number of terminations in given month / Average amount of employees in given month. 
However, if this is too much of a challenge it could also be:
Number of terminations in given month / Amount of employees at beginning of the month.

I am really struggling with the relations and the calculations to make this work.

Can someone please tell me step by step how to make this work. 
Would be huge 🙂 

[Anonymous · Accepted solution]

Hi @MDeemter 

Please try this:

Here I create a set of sample:

Then add a measure:

MEASURE =
VAR _currentdate =
    MAX ( 'Table'[Date] )
VAR _terminationsnumofmonth =
    COUNTROWS (
        FILTER (
            ALLSELECTED ( employe ),
            MONTH ( 'employe'[Termination Date] ) = MONTH ( _currentdate )
        )
    )
VAR _beginningofmonthnum =
    COUNTROWS (
        FILTER (
            ALLSELECTED ( employe ),
            'employe'[Starting Date]
                < DATE ( YEAR ( _currentdate ), MONTH ( _currentdate ), 1 )
        )
    )
        - COUNTROWS (
            FILTER (
                ALLSELECTED ( employe ),
                'employe'[Termination Date]
                    < DATE ( YEAR ( _currentdate ), MONTH ( _currentdate ), 1 )
            )
        )
RETURN
    _terminationsnumofmonth / _beginningofmonthnum

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MDeemter]

Strangely this works on your data. But if I copy the measure and fill in my table and columnnames, the percentages are all negatives and far from what they should be... 

[MDeemter]

Also I might add that the report has to be dynamic, so it should be possible to select a specific month with a slicer.