Employee Turnover / Dax Formulas / PowerPivot Model Suggestions

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,456
I'm pretty new to PowerPivot and I have been reviewing a lot of material regarding DAX functions. I know that what I would like to do is possible. I'm just struggling with how to implement it. I'm trying to provide a dynamic Annualized Employee Turnover Pivot Table. Ideally I would be able to plug and play and see turnover within different population segments by just using measures and calculated fields.

The calculation method for annualized employee turnover is: (YTD Terminations / Average(Prior Year Headcount, Current Year Headcount)) / Quarter Month End*12

So for example these variables yield Annualized Turnover of 37.1% :
BC
6Terms 864
7Prior Year HC 9,339
8Current Qtr HC 9,277
9Quarter Month End 3

<tbody>
</tbody>
Sheet1
My data looks like this (4 Tables):

The idea would be to take any of the green fields and drop them in the Rows of the Pivot Table and be able to get the turnover for that particular sub segment...

GHIJKLM
1YTD TerminationsCurrent HeadcountLast Year HeadcountDate
2Term Effective DateTerm Effective DateTerm Effective DateDate
3QuarterQuarterQuarter
Year
4Employee NameEmployee NameEmployee Name
Month
5Employee NumberEmployee NumberEmployee NumberDay
6Employee DepartmentEmployee Organization DepartmentEmployee Organization Department
WeekDayNr
7Employee Level DownEmployee Organization Level DownEmployee Organization Level Down
MonthNameShort
8Pay Grade
Pay Grade
Pay Grade
WeekDay
9Employee Ethnic Group DescriptionEmployee Ethnic Group DescriptionEmployee Ethnic Group Description
Quarter
10Employee Gender DescriptionEmployee Gender DescriptionEmployee Gender Description
YearMonth
11Sales IndicatorSales IndicatorSales Indicator
YearQuarter
12Event Sub Group DescriptionEvent Sub Group DescriptionEvent Sub Group Description
IsWeekend
13Total Annual Base SalaryTotal Annual Base SalaryTotal Annual Base Salary
DateKey
14Employee Location Country NameEmployee Location Country NameEmployee Location Country Name
YYYYMM
15Performance Rating DescriptionPerformance Rating DescriptionPerformance Rating Description
MonthStart
16GenerationGenerationGeneration
MonthEnd

<tbody>
</tbody>
Mapping
The Date table was made by using these suggestions:https://kohera.be/blog/business-int...te-table-in-powerpivot-with-one-single-value/

Data Looks like this:

2 Headcount tables (I can consolidate these if easier):
ABCDEFGHIJKLMNOPQR
1DateQuarterEmployee NameEmployee NumberFull Part Time NameEmployee DepartmentEmployee Level DownPay GradeTotal Annual Base Salary USDEmployee Ethnic Group DescriptionEmployee Gender DescriptionSales IndicatorPerformance Rating DescriptionEmployee Location Country NameLast Hire DateGenerationAge BandTenure Band
26/30/20172017 Q 2EE 11Full-TimeASDSoutheast RegionGrade 10$190,400.00WhiteMaleNON-SALESMeets Most ExpectationsUnited States4/29/1997Generation X40-49 years>=10 years
36/30/20172017 Q 2EE 22Full-TimeASDWest RegionGrade 5$18,000.00AsianMaleSALESNot RatedUnited States12/10/2001Baby Boomers50-59 years>=10 years
46/30/20172017 Q 2EE 33Full-TimeASDWest RegionGrade 6$85,859.00Hispanic or LatinoMaleNON-SALESExceeds ExpectationsUnited States4/30/2007Generation X50-59 years>=10 years
56/30/20172017 Q 2EE 44Full-TimeASDNorthwest RegionGrade 6$80,000.00Black or African AmericanMaleNON-SALESExceeds ExpectationsUnited States9/20/2006Generation X40-49 years>=10 years

<tbody>
</tbody>
Current /Last Year Headcount
Terms Table:
ABCDEFGHIJKLMNO
1Term Effective DateQuarterEmployee NameEmployee NumberEmployee DepartmentEmployee Level DownPay GradeEmployee Ethnic Group DescriptionEmployee Gender DescriptionSales IndicatorEvent Sub Group DescriptionTotal Annual Base SalaryEmployee Location Country NamePerformance Rating DescriptionGeneration
21/11/20172017 Q 1EE 2020ASDWest Region6WhiteMaleNON-SALESInvoluntary Termination$103,000.00United StatesNot GoodGeneration X
35/20/20172017 Q 2EE 88ASDSouth Region6WhiteFemaleNON-SALESVoluntary Termination$62,500.00United StatesOKGeneration X
44/20/20172017 Q 2EE 3636ASDSouth Region6Black or African AmericanFemaleNON-SALESInvoluntary Termination$61,650.00United StatesNot GoodGeneration X
53/21/20172017 Q 1EE 2525ASDSouth Region3WhiteFemaleNON-SALESVoluntary Termination$33,000.00United StatesNot GoodGeneration X
66/24/20172017 Q 2EE 3232ASDSouth Region3WhiteFemaleNON-SALESVoluntary Termination$34,000.00United StatesNot RatedGeneration X

<tbody>
</tbody>
YTD Terminations

Date Table (In Power Pivot Model):
PQRSTUVWXYZAAABACAD
1DateYearMonthDayWeekDayNrMonthNameShortWeekDayQuarterYearMonthYearQuarterIsWeekendDateKeyYYYYMMMonthStartMonthEnd
21/1/2016 0:002016116JanFriQ 12016 Jan2016 Q 1FALSE201601012016011/1/2016 0:001/31/2016 0:00
31/2/2016 0:002016127JanSatQ 12016 Jan2016 Q 1TRUE201601022016011/1/2016 0:001/31/2016 0:00
41/3/2016 0:002016131JanSunQ 12016 Jan2016 Q 1TRUE201601032016011/1/2016 0:001/31/2016 0:00

<tbody>
</tbody>
Mapping
Current Measures (Not sure if these are what I need...):
Term Count:=COUNTA('YTD Terms'[Employee Name]) 'Employees can sometimes term twice in a given period so we don't want a distinct count
CY HC:=DISTINCTCOUNT('CY HC'[Employee Number])
LY HC:=DISTINCTCOUNT('LY HC'[Employee Number])
AVG HC:=([LY HC]+[CY HC])/2

Hierarchies:
Department > Level Down
Year > Quarter > Month > Day

*Note the CY Headcount Date field will always be the current end of quarter. This could be used to calculate the annualization. If the two tables were combined it would be the max date in this column...

Can someone please provide some guidance on how to calculate annualized turnover based on the above scenario. I reviewed some of the content on this BlogPost that looks like it's close to what I want to do, but was not sure how to change it to fit my needs: https://powerpivotpro.com/2013/04/c...a-time-period-guest-post-from-chris-campbell/

I have a sample file mocked up if that would be easier to work with. It can be found here: DROPBOX FILE LINK
 
Last edited:

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,456
Going to bump this thread. Any help would be greatly appreciated. If someone could provide a little guidance to the method I may be able to implement it myself.
 

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,456
Matt-

Thanks for your response. I visited your link and downloaded the sample file and I believe I can see the direction you're going by identifying if an employee was in headcount at X point in time or if they had been termed at X point in time based on using some date criteria in measures (not exactly sure, but that's what direction I was thinking based on your example). Unfortunately, organizing the data in this format is not a possibility. In our database Hire Facts are not associated with Termination Facts, meaning I can't reconcile which records are associated with which... I could do this manually if I worked for a small company, but that's not the case. I can also see a number of scenarios where I would run into issues with people that have multiple jobs, multiple hires dates and multiple termination dates.

Is there any way I could accomplish my end goal (Dynamic Employee Turnover) within the current data structure described above?
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,113,777
Messages
5,544,174
Members
410,596
Latest member
JoeyZ
Top