cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Powereports
Helper I
Helper I

Retention calculation showing wrong results for time period

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
 

retention.JPG

1 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!!

View solution in original post

2 REPLIES 2
HashamNiaz
Super User
Super User

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

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

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!