Employee Turnover / Dax Formulas / PowerPivot Model Suggestions

mrmmickle1

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,461
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

<tbody>
[TD="align: center"]6[/TD]
[TD="bgcolor: #0070C0"]Terms[/TD]
[TD="align: center"] 864[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #0070C0"]Prior Year HC[/TD]
[TD="align: center"] 9,339[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #0070C0"]Current Qtr HC[/TD]
[TD="align: center"] 9,277[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #0070C0"]Quarter Month End[/TD]
[TD="align: center"] 3[/TD]

</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
Term Effective DateTerm Effective DateTerm Effective Date
QuarterQuarterQuarter
Employee NameEmployee NameEmployee Name
Employee NumberEmployee NumberEmployee Number
Employee DepartmentEmployee Organization DepartmentEmployee Organization Department
Pay GradePay GradePay Grade
Employee Ethnic Group DescriptionEmployee Ethnic Group DescriptionEmployee Ethnic Group Description
Total Annual Base SalaryTotal Annual Base SalaryTotal Annual Base Salary
Employee Location Country NameEmployee Location Country NameEmployee Location Country Name
Performance Rating DescriptionPerformance Rating DescriptionPerformance Rating Description

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #0070C0"]YTD Terminations[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #0070C0"]Current Headcount[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #0070C0"]Last Year Headcount[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #0070C0"]Date[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #00B050"]Date[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]
[/TD]
[TD="bgcolor: #000000"] Year [/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]
[/TD]
[TD="bgcolor: #000000"] Month [/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #000000"] Day [/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]
[/TD]
[TD="bgcolor: #000000"] WeekDayNr [/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #C6EFCE"]Employee Level Down[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #C6EFCE"]Employee Organization Level Down[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #C6EFCE"]Employee Organization Level Dow n [/TD]
[TD="align: right"]
[/TD]
[TD="bgcolor: #000000"] MonthNameShort [/TD]

[TD="align: center"] 8 [/TD]

[TD="align: right"]
[/TD]

[TD="align: right"]
[/TD]

[TD="align: right"]
[/TD]
[TD="bgcolor: #000000"] WeekDay [/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]
[/TD]
[TD="bgcolor: #000000"] Quarter [/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #C6EFCE"]Employee Gender Description[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #C6EFCE"]Employee Gender Description[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #C6EFCE"]Employee Gender Description[/TD]
[TD="align: right"]
[/TD]
[TD="bgcolor: #000000"] YearMonth [/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #C6EFCE"]Sales Indicator[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #C6EFCE"]Sales Indicator[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #C6EFCE"]Sales Indicator[/TD]
[TD="align: right"]
[/TD]
[TD="bgcolor: #000000"] YearQuarter [/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #C6EFCE"]Event Sub Group Description[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #C6EFCE"]Event Sub Group Description[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #C6EFCE"]Event Sub Group Description[/TD]
[TD="align: right"]
[/TD]
[TD="bgcolor: #000000"] IsWeekend [/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]
[/TD]
[TD="bgcolor: #000000"] DateKey [/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]
[/TD]
[TD="bgcolor: #000000"] YYYYMM [/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]
[/TD]
[TD="bgcolor: #000000"] MonthStart [/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #C6EFCE"]Generation[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #C6EFCE"]Generation[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #C6EFCE"]Generation[/TD]
[TD="align: right"]
[/TD]
[TD="bgcolor: #000000"] MonthEnd [/TD]

</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
6/30/20172017 Q 2EE 1Full-TimeASDSoutheast RegionGrade 10WhiteMaleNON-SALESMeets Most ExpectationsUnited StatesGeneration X40-49 years>=10 years
6/30/20172017 Q 2EE 2Full-TimeASDWest RegionGrade 5AsianMaleSALESNot RatedUnited StatesBaby Boomers50-59 years>=10 years
6/30/20172017 Q 2EE 3Full-TimeASDWest RegionGrade 6Hispanic or LatinoMaleNON-SALESExceeds ExpectationsUnited StatesGeneration X50-59 years>=10 years
6/30/20172017 Q 2EE 4Full-TimeASDNorthwest RegionGrade 6Black or African AmericanMaleNON-SALESExceeds ExpectationsUnited StatesGeneration X40-49 years>=10 years

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #0070C0"]Date[/TD]
[TD="bgcolor: #0070C0"]Quarter[/TD]
[TD="bgcolor: #0070C0"]Employee Name[/TD]
[TD="bgcolor: #0070C0"]Employee Number[/TD]
[TD="bgcolor: #0070C0"]Full Part Time Name[/TD]
[TD="bgcolor: #0070C0"]Employee Department[/TD]
[TD="bgcolor: #0070C0"]Employee Level Down[/TD]
[TD="bgcolor: #0070C0"]Pay Grade[/TD]
[TD="bgcolor: #0070C0"]Total Annual Base Salary USD[/TD]
[TD="bgcolor: #0070C0"]Employee Ethnic Group Description[/TD]
[TD="bgcolor: #0070C0"]Employee Gender Description[/TD]
[TD="bgcolor: #0070C0"]Sales Indicator[/TD]
[TD="bgcolor: #0070C0"]Performance Rating Description[/TD]
[TD="bgcolor: #0070C0"]Employee Location Country Name[/TD]
[TD="bgcolor: #0070C0"]Last Hire Date[/TD]
[TD="bgcolor: #0070C0"]Generation[/TD]
[TD="bgcolor: #0070C0"]Age Band[/TD]
[TD="bgcolor: #0070C0"]Tenure Band[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]$190,400.00[/TD]

[TD="align: right"]4/29/1997[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]$18,000.00[/TD]

[TD="align: right"]12/10/2001[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]$85,859.00[/TD]

[TD="align: right"]4/30/2007[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]4[/TD]

[TD="align: right"]$80,000.00[/TD]

[TD="align: right"]9/20/2006[/TD]

</tbody>
Current /Last Year Headcount​
Terms Table:
ABCDEFGHIJKLMNO
1/11/20172017 Q 1EE 20ASDWest RegionWhiteMaleNON-SALESInvoluntary TerminationUnited StatesNot GoodGeneration X
5/20/20172017 Q 2EE 8ASDSouth RegionWhiteFemaleNON-SALESVoluntary TerminationUnited StatesOKGeneration X
4/20/20172017 Q 2EE 36ASDSouth RegionBlack or African AmericanFemaleNON-SALESInvoluntary TerminationUnited StatesNot GoodGeneration X
3/21/20172017 Q 1EE 25ASDSouth RegionWhiteFemaleNON-SALESVoluntary TerminationUnited StatesNot GoodGeneration X
6/24/20172017 Q 2EE 32ASDSouth RegionWhiteFemaleNON-SALESVoluntary TerminationUnited StatesNot RatedGeneration X

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #0070C0"]Term Effective Date[/TD]
[TD="bgcolor: #0070C0"]Quarter[/TD]
[TD="bgcolor: #0070C0"]Employee Name[/TD]
[TD="bgcolor: #0070C0"]Employee Number[/TD]
[TD="bgcolor: #0070C0"]Employee Department[/TD]
[TD="bgcolor: #0070C0"]Employee Level Down[/TD]
[TD="bgcolor: #0070C0"]Pay Grade[/TD]
[TD="bgcolor: #0070C0"]Employee Ethnic Group Description[/TD]
[TD="bgcolor: #0070C0"]Employee Gender Description[/TD]
[TD="bgcolor: #0070C0"]Sales Indicator[/TD]
[TD="bgcolor: #0070C0"]Event Sub Group Description[/TD]
[TD="bgcolor: #0070C0"]Total Annual Base Salary[/TD]
[TD="bgcolor: #0070C0"]Employee Location Country Name[/TD]
[TD="bgcolor: #0070C0"]Performance Rating Description[/TD]
[TD="bgcolor: #0070C0"]Generation[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]20[/TD]

[TD="align: right"]6[/TD]

[TD="align: right"]$103,000.00[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]8[/TD]

[TD="align: right"]6[/TD]

[TD="align: right"]$62,500.00[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]36[/TD]

[TD="align: right"]6[/TD]

[TD="align: right"]$61,650.00[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]25[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]$33,000.00[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]32[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]$34,000.00[/TD]

</tbody>
YTD Terminations​

Date Table (In Power Pivot Model):
PQRSTUVWXYZAAABACAD
JanFriQ 12016 Jan2016 Q 1
JanSatQ 12016 Jan2016 Q 1
JanSunQ 12016 Jan2016 Q 1

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Year[/TD]
[TD="align: center"]Month[/TD]
[TD="align: center"]Day[/TD]
[TD="align: center"]WeekDayNr[/TD]
[TD="align: center"]MonthNameShort[/TD]
[TD="align: center"]WeekDay[/TD]
[TD="align: center"]Quarter[/TD]
[TD="align: center"]YearMonth[/TD]
[TD="align: center"]YearQuarter[/TD]
[TD="align: center"]IsWeekend[/TD]
[TD="align: center"]DateKey[/TD]
[TD="align: center"]YYYYMM[/TD]
[TD="align: center"]MonthStart[/TD]
[TD="align: center"]MonthEnd[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1/1/2016 0:00[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]

[TD="align: right"]FALSE[/TD]
[TD="align: right"]20160101[/TD]
[TD="align: right"]201601[/TD]
[TD="align: right"]1/1/2016 0:00[/TD]
[TD="align: right"]1/31/2016 0:00[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1/2/2016 0:00[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"]TRUE[/TD]
[TD="align: right"]20160102[/TD]
[TD="align: right"]201601[/TD]
[TD="align: right"]1/1/2016 0:00[/TD]
[TD="align: right"]1/31/2016 0:00[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1/3/2016 0:00[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]TRUE[/TD]
[TD="align: right"]20160103[/TD]
[TD="align: right"]201601[/TD]
[TD="align: right"]1/1/2016 0:00[/TD]
[TD="align: right"]1/31/2016 0:00[/TD]

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

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
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.
 
Upvote 0
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:
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Headcount Using Hire Date & Term Date Power Query
Replies
0
Views
668
goob90
G
M
  • Solved
Calculate headcount at the end of each month in 2019 based on hire and termination date
Replies
5
Views
5K
Peter_SSs
Peter_SSs
M
  • Solved
Formula to calculate headcount based on start/leave dates, governed by selectable dropdowns.
Replies
3
Views
9K
Toadstool
Toadstool
R
  • Question Question
Salary Distribution and 3 percent increment every year on hire date
Replies
0
Views
1K
ruchperformive
R
T
  • Question Question
How to use Cube functions to perform Sumifs ( sum range , Start Date array, <=specific month end, End Date array, >=specific month end)
Replies
4
Views
7K
Matt Allington
M

Forum statistics

Threads
1,223,885
Messages
6,175,184
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top