topic Re: Cummulative turnover rate based on turnover in month in DAX Commands and Tips

Cummulative turnover rate based on turnover in month

MDeemter — Tue, 01 Oct 2024 08:32:35 GMT

Hi fellow BI-enthusiasts,

I need some help with the calculation of the cummulative turnover rate for different BU's in my organisation.
I already have a measure that calculates the turnoverrate (number of terminated contracts/number of active employees).
This measure also works when I want the turnoverrate per Business Unit per month.

The standard within my organisation for reporting turnover is the cummulative percentage of given month and the 11 months prior.

However this is not as simple as it seems. Let me demonstrate with an example:

Business Unit X in February 2023:
- 66 active employees
- 2 employees left
- Turonoverrate of 3.03% (2/66)

In December 2023:
- 56 active employees
- 1 employee left
- Turnoverrate of 1.79% (1/56)

This is the data in the given months. However, in a selected month, I want the status of the current month and the 11 months before it cumulatively. This must be the sum of the different turonverrate's.

E.g. Business Unit X's cummulative percentage in December 2023 should be:
In this case 3.03% (Feb 2023) + 1.79% (Dec 2023) = 4.82% = CORRECT

So what I don't want is for everything to be added together like:
A total of 76 people were employed in those two months and 3 left employment, which gives you a ratio of 3.95% (3/76) = INCORRECT.

I hope this example clarifies my situation.

P.S. I might add that my data comes mostly form a SQL-server and is imported.
I tried several formules from Chat GPT, but they were either wrong or gave me the following error: "Visual has exceeded available resources", making it specifically hard to check whether chat GPT's formulas are correct.

Re: Cummulative turnover rate based on turnover in month

dharmendars007 — Tue, 01 Oct 2024 09:03:25 GMT

Hello MDeemter ,

Step1 - Create a Turnover Measure ( left employees /Active Employees)

TurnoverRate =
DIVIDE(
COUNTROWS(FILTER('Terminations', 'Terminations'[Business Unit] = 'Business Units'[Business Unit])),
COUNTROWS(FILTER('Active Employees', 'Active Employees'[Business Unit] = 'Business Units'[Business Unit])))

Step2 - Create the cumulative turnover rate over the past 12 months.

CumulativeTurnoverRate =
CALCULATE(
SUMX(
DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -12, MONTH),[TurnoverRate]),ALL('Calendar'[Date]))

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

Re: Cummulative turnover rate based on turnover in month

MDeemter — Tue, 01 Oct 2024 11:16:15 GMT

dharmendars007 thanks for the response,

So I'm a bit confused about this formula:
TurnoverRate =
DIVIDE(
COUNTROWS(FILTER('Terminations', 'Terminations'[Business Unit] = 'Business Units'[Business Unit])),
COUNTROWS(FILTER('Active Employees', 'Active Employees'[Business Unit] = 'Business Units'[Business Unit])))

I have two measures:
1. Headcount = a distinct count of employeenumber (with some filters as well) = from Facttable
2. Number of terminations in month = distinct count of employeenumber with a termination date in given month = From Dim.table

These measures are used in a third measure: Turnoverrate:

RatioUitDienst =

DIVIDE(

[Number of terminations in month],

[Headcount],

)

Re: Cummulative turnover rate based on turnover in month

dharmendars007 — Tue, 01 Oct 2024 11:44:12 GMT

MDeemter

Yes, You can create a separate measure as well using divide like you have done or do like one i shown do the percentage calculation by dividing in a single measure..any approach is fine

I suggest go with your approach since you have already creatd Termination and Headcount measure separately.

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

Re: Cummulative turnover rate based on turnover in month

MDeemter — Tue, 01 Oct 2024 13:51:37 GMT

dharmendars007 Yes, but the problem is: doing so does and using your formula CumulativeTurnoverRate does not give the right outcomes.

Re: Cummulative turnover rate based on turnover in month

Anonymous — Tue, 08 Oct 2024 02:46:24 GMT

Hi MDeemter , hello dharmendars007, thank you for your prompt reply!

Please try the following measure:

Measure = DIVIDE(SELECTEDVALUE('Table'[Termination Count]),SELECTEDVALUE('Table'[Employee Count]),0)

Rolling Measure = CALCULATE( SUMX('Table',[Measure]), FILTER( ALL('Table'), 'Table'[Date] <= MAX('Table'[Date]) && 'Table'[Date] >= EDATE(MAX('Table'[Date]), -11) && 'Table'[Business Unit] = MAX('Table'[Business Unit]) ) )

Best regards,

Joyce

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