Employee Turnover / Dax Formulas / PowerPivot Model Suggestions

[mrmmickle1]

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

	B	C
6	Terms	864
7	Prior Year HC	9,339
8	Current Qtr HC	9,277
9	Quarter 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...

	G	H	I	J	K	L	M
1	YTD Terminations		Current Headcount		Last Year Headcount		Date
2	Term Effective Date		Term Effective Date		Term Effective Date		Date
3	Quarter		Quarter		Quarter		Year
4	Employee Name		Employee Name		Employee Name		Month
5	Employee Number		Employee Number		Employee Number		Day
6	Employee Department		Employee Organization Department		Employee Organization Department		WeekDayNr
7	Employee Level Down		Employee Organization Level Down		Employee Organization Level Down		MonthNameShort
8	Pay Grade		Pay Grade		Pay Grade		WeekDay
9	Employee Ethnic Group Description		Employee Ethnic Group Description		Employee Ethnic Group Description		Quarter
10	Employee Gender Description		Employee Gender Description		Employee Gender Description		YearMonth
11	Sales Indicator		Sales Indicator		Sales Indicator		YearQuarter
12	Event Sub Group Description		Event Sub Group Description		Event Sub Group Description		IsWeekend
13	Total Annual Base Salary		Total Annual Base Salary		Total Annual Base Salary		DateKey
14	Employee Location Country Name		Employee Location Country Name		Employee Location Country Name		YYYYMM
15	Performance Rating Description		Performance Rating Description		Performance Rating Description		MonthStart
16	Generation		Generation		Generation		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):

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Date	Quarter	Employee Name	Employee Number	Full Part Time Name	Employee Department	Employee Level Down	Pay Grade	Total Annual Base Salary USD	Employee Ethnic Group Description	Employee Gender Description	Sales Indicator	Performance Rating Description	Employee Location Country Name	Last Hire Date	Generation	Age Band	Tenure Band
2	6/30/2017	2017 Q 2	EE 1	1	Full-Time	ASD	Southeast Region	Grade 10	$190,400.00	White	Male	NON-SALES	Meets Most Expectations	United States	4/29/1997	Generation X	40-49 years	>=10 years
3	6/30/2017	2017 Q 2	EE 2	2	Full-Time	ASD	West Region	Grade 5	$18,000.00	Asian	Male	SALES	Not Rated	United States	12/10/2001	Baby Boomers	50-59 years	>=10 years
4	6/30/2017	2017 Q 2	EE 3	3	Full-Time	ASD	West Region	Grade 6	$85,859.00	Hispanic or Latino	Male	NON-SALES	Exceeds Expectations	United States	4/30/2007	Generation X	50-59 years	>=10 years
5	6/30/2017	2017 Q 2	EE 4	4	Full-Time	ASD	Northwest Region	Grade 6	$80,000.00	Black or African American	Male	NON-SALES	Exceeds Expectations	United States	9/20/2006	Generation X	40-49 years	>=10 years

<tbody>
</tbody>

Current /Last Year Headcount​

Terms Table:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Term Effective Date	Quarter	Employee Name	Employee Number	Employee Department	Employee Level Down	Pay Grade	Employee Ethnic Group Description	Employee Gender Description	Sales Indicator	Event Sub Group Description	Total Annual Base Salary	Employee Location Country Name	Performance Rating Description	Generation
2	1/11/2017	2017 Q 1	EE 20	20	ASD	West Region	6	White	Male	NON-SALES	Involuntary Termination	$103,000.00	United States	Not Good	Generation X
3	5/20/2017	2017 Q 2	EE 8	8	ASD	South Region	6	White	Female	NON-SALES	Voluntary Termination	$62,500.00	United States	OK	Generation X
4	4/20/2017	2017 Q 2	EE 36	36	ASD	South Region	6	Black or African American	Female	NON-SALES	Involuntary Termination	$61,650.00	United States	Not Good	Generation X
5	3/21/2017	2017 Q 1	EE 25	25	ASD	South Region	3	White	Female	NON-SALES	Voluntary Termination	$33,000.00	United States	Not Good	Generation X
6	6/24/2017	2017 Q 2	EE 32	32	ASD	South Region	3	White	Female	NON-SALES	Voluntary Termination	$34,000.00	United States	Not Rated	Generation X

<tbody>
</tbody>

YTD Terminations​

Date Table (In Power Pivot Model):

	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	Date	Year	Month	Day	WeekDayNr	MonthNameShort	WeekDay	Quarter	YearMonth	YearQuarter	IsWeekend	DateKey	YYYYMM	MonthStart	MonthEnd
2	1/1/2016 0:00	2016	1	1	6	Jan	Fri	Q 1	2016 Jan	2016 Q 1	FALSE	20160101	201601	1/1/2016 0:00	1/31/2016 0:00
3	1/2/2016 0:00	2016	1	2	7	Jan	Sat	Q 1	2016 Jan	2016 Q 1	TRUE	20160102	201601	1/1/2016 0:00	1/31/2016 0:00
4	1/3/2016 0:00	2016	1	3	1	Jan	Sun	Q 1	2016 Jan	2016 Q 1	TRUE	20160103	201601	1/1/2016 0:00	1/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]

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]

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]

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?