# Employee Turnover / Dax Formulas / PowerPivot Model Suggestions



## mrmmickle1 (Sep 6, 2017)

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% :

BC6Terms 8647Prior Year HC 9,3398Current Qtr HC 9,2779Quarter 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...


GHIJKLM1YTD TerminationsCurrent HeadcountLast Year HeadcountDate2Term Effective DateTerm Effective DateTerm Effective DateDate3QuarterQuarterQuarter
Year4Employee NameEmployee NameEmployee Name
Month5Employee NumberEmployee NumberEmployee NumberDay6Employee DepartmentEmployee Organization DepartmentEmployee Organization Department
WeekDayNr7Employee Level DownEmployee Organization Level DownEmployee Organization Level Down
MonthNameShort8Pay Grade
Pay Grade
Pay Grade
WeekDay9Employee Ethnic Group DescriptionEmployee Ethnic Group DescriptionEmployee Ethnic Group Description
Quarter10Employee Gender DescriptionEmployee Gender DescriptionEmployee Gender Description
YearMonth11Sales IndicatorSales IndicatorSales Indicator
YearQuarter12Event Sub Group DescriptionEvent Sub Group DescriptionEvent Sub Group Description
IsWeekend13Total Annual Base SalaryTotal Annual Base SalaryTotal Annual Base Salary
DateKey14Employee Location Country NameEmployee Location Country NameEmployee Location Country Name
YYYYMM15Performance Rating DescriptionPerformance Rating DescriptionPerformance Rating Description
MonthStart16GenerationGenerationGeneration
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):

ABCDEFGHIJKLMNOPQR1DateQuarterEmployee 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 Band26/30/20172017 Q 2EE 11Full-TimeASDSoutheast RegionGrade 10$190,400.00WhiteMaleNON-SALESMeets Most ExpectationsUnited States4/29/1997Generation X40-49 years>=10 years36/30/20172017 Q 2EE 22Full-TimeASDWest RegionGrade 5$18,000.00AsianMaleSALESNot RatedUnited States12/10/2001Baby Boomers50-59 years>=10 years46/30/20172017 Q 2EE 33Full-TimeASDWest RegionGrade 6$85,859.00Hispanic or LatinoMaleNON-SALESExceeds ExpectationsUnited States4/30/2007Generation X50-59 years>=10 years56/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:

ABCDEFGHIJKLMNO1Term Effective DateQuarterEmployee NameEmployee NumberEmployee DepartmentEmployee Level DownPay GradeEmployee Ethnic Group DescriptionEmployee Gender DescriptionSales IndicatorEvent Sub Group DescriptionTotal Annual Base SalaryEmployee Location Country NamePerformance Rating DescriptionGeneration21/11/20172017 Q 1EE 2020ASDWest Region6WhiteMaleNON-SALESInvoluntary Termination$103,000.00United StatesNot GoodGeneration X35/20/20172017 Q 2EE 88ASDSouth Region6WhiteFemaleNON-SALESVoluntary Termination$62,500.00United StatesOKGeneration X44/20/20172017 Q 2EE 3636ASDSouth Region6Black or African AmericanFemaleNON-SALESInvoluntary Termination$61,650.00United StatesNot GoodGeneration X53/21/20172017 Q 1EE 2525ASDSouth Region3WhiteFemaleNON-SALESVoluntary Termination$33,000.00United StatesNot GoodGeneration X66/24/20172017 Q 2EE 3232ASDSouth Region3WhiteFemaleNON-SALESVoluntary Termination$34,000.00United StatesNot RatedGeneration X

<tbody>

</tbody>*YTD Terminations*​
Date Table (In Power Pivot Model):

PQRSTUVWXYZAAABACAD1DateYearMonthDayWeekDayNrMonthNameShortWeekDayQuarterYearMonthYearQuarterIsWeekendDateKeyYYYYMMMonthStartMonthEnd21/1/2016 0:002016116JanFriQ 12016 Jan2016 Q 1FALSE201601012016011/1/2016 0:001/31/2016 0:0031/2/2016 0:002016127JanSatQ 12016 Jan2016 Q 1TRUE201601022016011/1/2016 0:001/31/2016 0:0041/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


----------



## mrmmickle1 (Sep 8, 2017)

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.


----------



## Matt Allington (Sep 10, 2017)

A headcount table is a static snapshot at a point in time. If possible you should get an employee table that contains a hire and depart date columns. From there you can use the technique I use here to extract the data you need. 
https://powerpivotpro.com/2016/12/how-many-working-days-has-an-employee-been-off-work/


----------



## mrmmickle1 (Sep 12, 2017)

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?


----------

