Headcount Time Travel

[sokatenaj]

Hi Folks,

I have an idea for an effort that I want to implement in Power BI. I have 3 tables: 1.) Active Employees 2.) Terminations 3.) Calendar Table. 

The Active & Termination tables have a field called "Effective Date" which is the effective date of the transaction of them becoming an employee or terminating. The calendar table is a table I created to create a relationship between the 3 tables based on dates.  So Effective Date field in Active Employees is linked to Date field in Calendar, and Effective date field in Terminations is linked to same Date field in Calendar. 

In my PBIX file, I have 2 tabs/pages: People Inisights and Terminations.  People Insights gives current data of active employees using different visuals like pie charts, tiles, bar charts, etc for different HR metrics like % Race, Equity by Race, Gender spread, etc. The termination tab/page has similar data in similar carve-outs.

What I want to do is to create a 3rd tab/page called "Time Travel" and I want the same visuals on the People Insights tab and do a point in time visual. 

So for example, say Michael Jordan has a team of 500 people in May 2015. Today is July 2017 and is headcount is 440. Since May 2015, he lost 85 people, but gained 25. So our head count is 440 which would be displayed on the People Insights tab. What I want to do is say, let's look at his workforce in March 2016. This would roll the terms that left after March 2016 into that number as long as they were HIRED before March 2016. Does this make sense?

What would I need to do to implement this? The same fields are in both Active and Terminations table, but the data lives in two separate tables because that is how our data is managed here. Would I need to merge these to one table? If so, how would I do that in Power BI? I know this is a heavy lift. 

Any help would be great. Please and thank you!

[MalS]

This should work out well in Power BI provided you have the 'hire date' of all employees - even those that have since been fired. However, from reading your post, I am not sure that is the case? 

It sounds like the 'Active' table contains only those still employeed at the company, and the "Effective date" in this table is the date of hire. And those that have been fired show up in the Termination table, with an "Effective Date" field that represents the termination date. But does this table also include the original date of hire?

Can you provide some sample (anonymized) data for use to look at?

[sokatenaj]

Hi @MalS,

The Hire Date fields are in both tables, as well. That is correct. Active are those that are still employed in the company. Terms are those that have left. 

[MalS · Accepted solution]

So here's one way to get what you are looking for:

1. Open the Query Editor and click Append Queries to join the two tables together. Give the new table a name (like 'Time Travel')

2. Add a conditional column to show the Termination date. like this:

3. Make sure the Termination Date column data type is set to Date.

4. Add a new date table called 'Time Travel Date'. An easy way to do this is click New Table and enter:

                    Time Travel Date = CALENDARAUTO()

5. The Time Travel Date table will act as a "disconnected slicer", so you don't need to set up any relationships on this table

6. Add some new measures:

Time Travel Date = MIN('Time Travel Date'[Date])

Time Travel Employees = CALCULATE(COUNT('Time Travel'[EE ID]), 
     FILTER('Time Travel', 'Time Travel'[Hire Date] < [Time Travel Date]), 
     FILTER('Time Travel',ISBLANK('Time Travel'[Termination Date]) || 'Time Travel'[Termination Date] > [Time Travel Date])) 

The first measure 'harvests' a date to use as the 'time travel date'. You can add a filter to your page so users can interactively select this date. (Note that if you pick more than one date, the measure above is set up to pick the earliest date)

The second measure counts the employees with a hire date that is earlier than the 'time travel date' and a termination that is after the 'time travel date' (or blank, indicating they have not been terminated).

You can add the 'Time Travel Employees' measure and the Gender field to a pie chart, for example, to show the gender split at the 'time travel date'.

Hope that helps.

Mal

[sokatenaj]

Wow @MalS! Thanks so much. This is super helpful and a huge huge huge help.

What's strange is that I almost thought to do it this way but almost thought that it would not work.

My only thing is that how you got the "Time Travel Date" measure to work as a slicer? If I use the "Date" field in the Time Travel Date it doesn't filter the pie. How do I get the Time Travel Date measure to work as a date slicer? 

Many thanks! 

[sokatenaj]

Nevermind! I had an ID10T error! 🙂 LOL!!! THANKS SO MUCH! This works really well.