cancel
Showing results for
Search instead for
Did you mean:
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

Accepted Solutions
Member

## Re: Retention Rate (HR)

Hi James,

Here's my solution:

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

2. Connect the table to your 'Calendar' table

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 matrix

hope it helps,

Pawel

Member

## Re: Retention Rate (HR)

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.

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

Pawel

6 REPLIES 6
Super User

## Re: Retention Rate (HR)

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.

Frequent Visitor

## Re: Retention Rate (HR)

Hi Ashish

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

Thanks,

James

Super User

## Re: Retention Rate (HR)

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

Member

## Re: Retention Rate (HR)

Hi James,

Here's my solution:

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

2. Connect the table to your 'Calendar' table

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 matrix

hope it helps,

Pawel

Frequent Visitor

## Re: Retention Rate (HR)

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

Member

## Re: Retention Rate (HR)

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.

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

Pawel

## Helpful resources

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 44 members 845 guests
Recent signins:
Please welcome our newest community members: