HR Turnover/Attrition Woes

[Anonymous]

Hi Folks,

This is a rather lengthy post -- I apologize. I have to start doing turnover by month using two feeds: Active_Heads & Term_Heads. The way I was doing it before was in Excel and now I need to do it in Power BI and while I can create an average headcount measure for each month, I feel like there needs to be an easier, dynamic way -- especially since I need to report quarterly and yearly turnover! 

My Active_Heads report looks like this:

EE ID	Name	Title	Dept	Eff Month
111121	Bell, Jingle	Cheery Song	North Pole	1-Dec-17
12154	Claus, Santa	Jolly Saint Nick	North Pole	1-Dec-17
123131	Frost, Jack	Winter Mogul	Alaska	1-Dec-17
111121	Bell, Jingle	Cheery Song	North Pole	31-Dec-17
12154	Claus, Santa	Jolly Saint Nick	North Pole	31-Dec-17
123131	Frost, Jack	Winter Mogul	Alaska	31-Dec-17
111121	Bell, Jingle	Cheery Song	North Pole	1-Jan-18
12154	Claus, Santa	Jolly Saint Nick	North Pole	1-Jan-18
123131	Frost, Jack	Winter Mogul	Alaska	1-Jan-18
111121	Bell, Jingle	Cheery Song	North Pole	31-Jan-18

The report captures all employees active at the beginning of each month and all employees at the end of each month using an "Effective Month" date.  So you see employee Jingle Bell, Jack Frost, and Santa are active all of December and most of January. However, Santa and Jack termed so they will move from the active report on to the term report as notated below:

EE ID	Name	Title	Status	Eff Date
545464	Claus, Mrs.	Wife - Jolly Saint Nick	Voluntary	1-Dec-17
12154	Claus, Santa	Jolly Saint Nick	Voluntary	12-Jan-18
123131	Frost, Jack	Winter Mogul	Involuntary	22-Jan-18

The term report is one long report with everyone's terms going back 2-3 years. 

So now for the hard part. I need to take the average headcount of each month using the formula: # of Terms for the month / ((HC Start of Month + Headcount End of Month)/2) So for example, December would be 1 / ((3 + 3)/2) which would give me 33% Turnover. I then need to break it out by Voluntary and Involuntary for each month, quarter, and year. 

So in a table I'd want to show Start HC for Month, Ending HC for Month, Avg Headcount, # Total Terms, #Vol Terms, #Invol Terms

and then in a separate table I'd want to show the Total T/O, Total Vol, Total Invol for each month.

What is the best way to set this up? I'm so stumped. 😞  I just can't imagine I have to do separate measures for each month. 

Thanks so much!!!!!!!!!

[Greg_Deckler · Accepted solution]

Can't do the voluntary/involuntary because no sample data, not sure how that gets related in.

But, with your data provided and 3 calculated columns:

Month = FORMAT([Eff Month],"mmmm")

Month Sort = MONTH([Eff Month])

Year = YEAR([Eff Month])

And then 4 measures:

Start HC for Month = 
VAR __table = FILTER('Table2',[Eff Month] = MIN([Eff Month]))
RETURN
COUNTROWS(__table)

End HC for Month = 
VAR __table = FILTER('Table2',[Eff Month] = MAX([Eff Month]))
RETURN
COUNTROWS(__table)

Avg Headcount = DIVIDE([Start HC for Month] + [End HC for Month],2,0)

Total Terms = [Start HC for Month] - [End HC for Month]

See attached, Page 1, Table2

[Anonymous]

@Greg_Deckler Thanks so much for this. This is very helpful and I wouldn't have thought to format it in such a way.

The terms are the 3 people listed in the 2nd table in the post that are Voluntary and Involuntary.  Total terms should be the count of terms each month overall and then we also need to count how many are voluntary and how many are involuntary separately because I need to report on overall turnover each month and then separately voluntary and involuntary. 

Thanks so much for your help. 

[Greg_Deckler]

OK, so those are two separate fact tables in your data set the? Sorry, I think I got confused because you called those reports, so I was thinking there was an underlying fact table or dimension. If you can confirm, I'll see what I can do.

[Anonymous]

Hi! Yes, they are two separate tables. However, your measures and calculations work fine for the active data so far. The term data, I assume I can just create 3 term measures on the term table that counts total terms, total vol, and total invol using something like:

# Terms = COUNT(Terms[Employee Number])

# Involuntary =

CALCULATE (

COUNT ( 'Terms'[Leaving Group] ),

'Terms'[Leaving Group] = "Involuntary"

)

Then I would need to create 3 separate measures for turnover such as:

Total T/O = DIVIDE([# Terms], [Avg HC])

Total Invol T/O = DIVIDE([# Involuntary], [Avg HC]) etc.

Or is there an easier way? I plan to put these on a matrix table and graphs. 

Thanks, Greg!

[Anonymous]

@Greg_Deckler  Fab! One other question.

I just got a request to also add "Today's Turnover" Unfortunately, "Today's headcount" is a separate table identical to the first table in my post, but without the "Eff Month" field.  The main table in my post is a historical table IT sends me each month with Eff Month date stamp on it identifying active people as of the 1st of that month and active people as of the last day of the month.

Outside of just creating a single month measure for that current month, is there a way to do this dynamically? So for example, since we are in March, we have the march 1 snapshot on that historical table/main table but it's march 18. How could I do this on a dynamic basis? Essentially the avg headcount would then be (Start HC for Month + Today HC for month)/2. And then would need to incorporate turnover logic to say if we are in the current month to use "Today's HC" in lieu of Ending HC. 

Did I make sense? LOL

[Greg_Deckler]

So, the "today" table is a separate table and only includes terms for the current day only? Is that correct?

[Anonymous]

Hi Greg, 

It's a separate table with today's active data (same setup as first table in post, minus the 'Eff Month' field because it isn't a historical table). Terms for that day still also show up on the term table listed in my post, too (the 2nd table in my post). For total terms, I used the measure I included and then also separated them out by involuntary terms and voluntary terms as separate measures. 

[Anonymous]

@Greg_Deckler Have you had any luck trying to roll the "Today" headcount in when in current month/day? 

[Greg_Deckler]

Nope, I think that should do it!