Retention calculation showing wrong results for time period

[Powereports]

Hi,

 I have a problem with my retention rate calculation in that it shows negative values for a time period from Sep - May. But the monthly retention rates are working fine .

I understand that we are hiring many staff for a role , but not sure why the measure is skewing up and showing negative values.

As in the screenshot below we can observe the Retention for the entire time period from Sep 20 - may 21 is -128%.

Below are the dax measures used for the Retention% calculation. It would be great if you can have a look at the measures and let me know if there is some error.

1. Active -SOP(Active start of the Period):

VAR _FirstDayCurrentMonth = MIN( Calendar [Date] )

RETURN

CALCULATE (

COUNT ('Table1'[EMPLID]),

FILTER (

'Table1','Table1'[LAST_HIRE_DT]<= _FirstDayCurrentMonth

&& ('Table1'[TERMINATION_DT] >= _FirstDayCurrentMonth || ISBLANK ( 'Table1''[TERMINATION_DT]))))

2. EOP(End of Period) Active staff =

VAR _lastdayCurrentMonth = Max( Calendar[Date] )

RETURN

CALCULATE (COUNT ('Table1'[EMPLID]),

FILTER ('Table1','Table1'[LAST_HIRE_DT] <= _lastdayCurrentMonth

&& ('Table1'[TERMINATION_DT]>= _lastdayCurrentMonth || ISBLANK ( 'Table1'[TERMINATION_DT]))))

3. Terminations =

CALCULATE(COUNT(EMPLID),

USERELATIONSHIP(Turnover[Date],Calendar[Date]))

4. Staff Retained = [SOP Active staff] - [Terminations]

5. Retention % = DIVIDE([Staff Retained],[SOP Active staff],0) *100

[HashamNiaz]

Hi @Powereports !

The obvious problem is not just with [Retention %] but with all the measure at Total level.

So at Grand Total your [Active SOP] is 14 and your [Terminations] is 32, which yeilds -18 as [Staff Retained]. This messed up your retentiion % calculation as well.

Try fixing [Active SOP] measure, alternatively you can use [Active EOP] measure for calculating [Staff Retained] & [Retention %] calculation.

Regards,

Hasham

[Powereports · Accepted solution]

Hi Hasham,

 That's true. But I have validated that the Active SOP and the number seems correct and the termination number is higher because as we hired more and more staff therefore termination has resulted in a higher number due to the new hired staff.

 At this point I am looking at a measure which gives me retained staff which is a direct calculation and is not dependent on the measures ACTIVE SOP and Termination.

Would you be able to help me formulate a independent staff retained measure which gives staff who were active during the start of the period (Sep 20) and are still with the company for a time period selected.

Thanks in advance!!