Forum Discussion
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
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.
2. The table may include other fields (Gender, Type, etc). that will sum up to total see attached pbix
Pawel