Headcount Time Travel

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