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

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