topic Re: Calculating Employee turnover rate per month in DAX Commands and Tips

Calculating Employee turnover rate per month

MDeemter — Mon, 12 Aug 2024 08:06:03 GMT

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 🙂

Re: Calculating Employee turnover rate per month

MDeemter — Mon, 12 Aug 2024 08:09:49 GMT

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

Re: Calculating Employee turnover rate per month

Anonymous — Tue, 13 Aug 2024 03:09:45 GMT

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.

Re: Calculating Employee turnover rate per month

MDeemter — Tue, 27 Aug 2024 11:22:55 GMT

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...