Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Consultant001
Frequent Visitor

Retention Rate (HR)

Hi All

I need to calculate the retention rate of staff (12 months of Terminated/Current staff total).  

I can get the filters to show the information on separate pages, Terminated by month (Because I've created groups) and the total current employees (Because of the filter "Before"), but I'm unable to make this work on the single page for retention rate.  

I presume I have to make a DAX Equation.  CALCULATE()....

1) Where the terminated 12-month would be based on the MAX date with 12 months prior as a range.  

2) And another for the current employees, (which is Hired-Terminated) ignoring all dates/filters except the MAX date.

Would you be able to help?

I have:

Hired Date

Terminated Date or Blank

Employee No.

Date table

 

Data via this link:

https://docs.google.com/spreadsheets/d/114xZ57xFR8PgI1Vzqd2hkiscjv695D4MDMn4u97venQ/edit?usp=sharing

2 ACCEPTED SOLUTIONS
pawel1
Skilled Sharer
Skilled Sharer

Hi James,

Here's my solution:

1. unpivot your data table in Query Editor to look like this:retention data unpivoted.PNG

 2. Connect the table to your 'Calendar' table

retention model1.JPG

3. Create a couple of basic DAX measures:

 

count hired =
CALCULATE(
    COUNT(Table1[Employee No]),
    Table1[Attribute]="Hired Date")

 

count terminated =
CALCULATE(
    COUNT(Table1[Employee No]),
    Table1[Attribute]="Terminated Date")

 

cumulative hired = CALCULATE([count hired],
    FILTER(ALLSELECTED('Calendar'[Date]),
        'Calendar'[Date]<MAX('Calendar'[Date])))

 

cumulative terminated = CALCULATE([count terminated],
    FILTER(ALLSELECTED('Calendar'[Date]),
        'Calendar'[Date]<MAX(Calendar[Date])))

 

Current Staff = [cumulative hired]-[cumulative terminated]

redemption rate = DIVIDE([count terminated],[Current Staff],0)

 (tipp: you can use VAR to condense everything to 1-2 measures)

 

4. Put on matrixredempion matrix.JPG

 hope it helps,

Pawel

 

 

View solution in original post

James,

1. Unpivot is a step in Query Editor, you do it once, then it occurs automaticaly every time you refresh.  Simply mark the two columns 'Hired Date' and 'Terminated Date' and unpivot them.  No need to change anything in your dataset.  retention unpivot.JPG

 

 

 

2. The table may include other fields (Gender, Type, etc). that will sum up to total retention matrix1.JPG see attached pbix

 

Pawel

View solution in original post

8 REPLIES 8
pawel1
Skilled Sharer
Skilled Sharer

Hi James,

Here's my solution:

1. unpivot your data table in Query Editor to look like this:retention data unpivoted.PNG

 2. Connect the table to your 'Calendar' table

retention model1.JPG

3. Create a couple of basic DAX measures:

 

count hired =
CALCULATE(
    COUNT(Table1[Employee No]),
    Table1[Attribute]="Hired Date")

 

count terminated =
CALCULATE(
    COUNT(Table1[Employee No]),
    Table1[Attribute]="Terminated Date")

 

cumulative hired = CALCULATE([count hired],
    FILTER(ALLSELECTED('Calendar'[Date]),
        'Calendar'[Date]<MAX('Calendar'[Date])))

 

cumulative terminated = CALCULATE([count terminated],
    FILTER(ALLSELECTED('Calendar'[Date]),
        'Calendar'[Date]<MAX(Calendar[Date])))

 

Current Staff = [cumulative hired]-[cumulative terminated]

redemption rate = DIVIDE([count terminated],[Current Staff],0)

 (tipp: you can use VAR to condense everything to 1-2 measures)

 

4. Put on matrixredempion matrix.JPG

 hope it helps,

Pawel

 

 

bcohen
Frequent Visitor

Retention is defined as the number remaining from the previous year.  See SHRM.

If you unpivot the data, wouldn't that disrupt other analysis or are you making a copy of the data table and then unpivoting?

Wow, thank you, Pawel!

 

Is it possible to be completed the analysis without unpivoting the data?  Or will unpivot occur automatically every time the data is updated?

 

Secondly, how would I create:

Gender Profile (Female: Male ratio) and;

Employee Count (FT, PT, CAS)

 

Both should be summed so that they match the current staff.  Example if current staff = 451 then F= 51, M=400 and same with FT, PT and CAS.

 

I’ve added the additional information to the data set.

 

Thanks

James,

1. Unpivot is a step in Query Editor, you do it once, then it occurs automaticaly every time you refresh.  Simply mark the two columns 'Hired Date' and 'Terminated Date' and unpivot them.  No need to change anything in your dataset.  retention unpivot.JPG

 

 

 

2. The table may include other fields (Gender, Type, etc). that will sum up to total retention matrix1.JPG see attached pbix

 

Pawel

Ashish_Mathur
Super User
Super User

Hi,

 

Based on the Table that you have shaed in the Speadsheet, please show the exact expected result and the calculation logic.  I would like to compare my answer with yours.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish

 

Great thanks.  I've attached a "retention rate by year" for comparison.

 

Capture1.PNG

 

Thanks,

James

I do not understand.  I'll request someone else to help you.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.