Dynamic Headcount Analysis using Dax

[DataNinja777]

While headcount analysis seems a conceptually straightforward topic, to do it flexibly, for example, by preparing a dynamic list of employees at any given point in time, contains some technically challenging aspects. 

However, with the analytical capabilities afforded by DAX, a robust headcount report spanning across multiple years that can flexibly provide a snapshot of the number of employees and their names, tenure, and age at any historical point in time can be prepared with ease, from a basic employee database containing information on hiring and leaving dates.

I am using the employee table as a fact table rather than a dimension table so that multiple dates of joining, employee affiliations to different departments and different roles within the organization at different points in time can all be displayed. It is a fact that employees can transfer between departments, change their roles over the course of years as well as leave the organization and come back again at a later date at some point in the future.

To create an interactive headcount dashboard, you only need two tables,

• An employee fact table
• A calendar table

🔗❌A Disconnected Calendar Table:  the key to the headcount analysis  🔑

To enable slicing of headcount by time-dimension at any point in time, we have to keep the calendar table and employee fact table as disconnected tables.

The DAX formula below provides a flexible headcount number at any historical point in time:

Headcount =
SUMX (
    EmployeeFact,
    IF (
        EmployeeFact[Start] <= [SelectedDate]
            && EmployeeFact[End] >= [SelectedDate],
        1,
        BLANK ()
    )
)

The above formula only sums up the number of employees from the employee database who were at the company at a given point in time (i.e., joined on or earlier than the given point in time), who at the same time, have not left the company, (i.e., left the company on or after that given point in time).

Where:

SelectedDate =
MAX ( 'Calendar'[Date] )

Incorporating year and month to the row and column labels respectively in the matrix format creates a table of historical month-end headcount which can be cross-filtered by gender, role, location, and department.

I’ve also added a measure of the list of employee names using the ConcatenateX function:

Employee list =
CONCATENATEX (
    EmployeeFact,
    IF (
        EmployeeFact[Start] <= [SelectedDate]
            && EmployeeFact[End] >= [SelectedDate],
        EmployeeFact[Employee]
    )
)

But then, I decided instead that I’d like to add the age in brackets next to the employee’s name, and it turns out that ConcatenateX can handle such spontaneous tweaking with ease.

Employee list =
CONCATENATEX (
    EmployeeFact,
    IF (
        EmployeeFact[Start] <= [SelectedDate]
            && EmployeeFact[End] >= [SelectedDate],
        EmployeeFact[Employee] & " ("
            & ROUNDDOWN ( [Employee average age], 0 ) & ") "
    )
)

Where

Employee average age =
AVERAGEX (
    EmployeeFact,
    IF (
        EmployeeFact[Start] <= [SelectedDate]
            && EmployeeFact[End] >= [SelectedDate],
        YEARFRAC ( [Birthday], [SelectedDate] ),
        BLANK ()
    )
)

The DAX formula on employee average age just calculates the age of the employees as at the selected date as the difference between that selected date and the birthday, where the employee has joined on or before the selected date and left on or after the selected date, (i.e., if they are employed by the company at the selected date.) Although the measure name says [Employee average age], when only one employee is involved as in the case of the employee name list, it just shows the age of that employee at the selected time point.

Including the department in the row labels, and role as a column label in another matrix table, we can obtain a list of employees with their age in brackets by department and by role at any given historical point in time which can be cross-filtered with the month-end headcount matrix table above.

Similar to the employee average age measure, I’ve also added an employee tenure measure:

Employee tenure =
AVERAGEX (
    EmployeeFact,
    IF (
        EmployeeFact[Start] <= [SelectedDate]
            && EmployeeFact[End] >= [SelectedDate],
        YEARFRAC ( [Start], [SelectedDate] ),
        BLANK ()
    )
)

I then added joiners and leavers measures.

Joiners =
SUMX (
    EmployeeFact,
    IF (
        EmployeeFact[Start] <= [SelectedDate]
            && EmployeeFact[Start] > [SelectedDate (Start)],
        1,
        0
    )
)

Where

SelectedDate (Start) =
MIN ( ‘Calendar'[Date] ) – DAY ( MIN ( ‘Calendar'[Date] ) )

Similarly for leavers,

Leavers =
SUMX (
    EmployeeFact,
    IF (
        EmployeeFact[End] <= [SelectedDate]
            && EmployeeFact[End] > [SelectedDate (Start)],
        1,
        0
    )
)

But then, I decided that I’d like to see the numbers and names of joiners and leavers on the dashboard in a similar manner as the headcount snapshot. To show this on the one-page dashboard, I used another disconnected table called “Slicer,” and enabled selecting measures using a switch function (a “Harvester” measure).

Selected Headcount Measure =
SWITCH ( [Selected Slicer], 1, [Headcount], 2, [Joiners], 3, [Leavers] )

Where

Selected Slicer =
MIN ( Slicer[#] )

To reflect this change, I tweaked the [Employee average age] measure and the [Employee tenure] measure and replaced the formula calculating the headcount as at the selected date to [Selected Headcount Measure].

Employee average age =
AVERAGEX (
    EmployeeFact,
    IF (
        [Selected Headcount Measure] <> BLANK (),
        YEARFRAC ( [Birthday], [SelectedDate] ),
        BLANK ()
    )
)

I’ve created measures for the joiners and leavers’ name lists with the age in brackets.

Joiner list =
CONCATENATEX (
    EmployeeFact,
    IF (
        EmployeeFact[Start] <= [SelectedDate]
            && EmployeeFact[Start] > [SelectedDate (Start)],
        EmployeeFact[Employee] & " ("
            & ROUNDDOWN ( [Employee average age], 0 ) & ") "
    )
)

Leaver list =
CONCATENATEX (
    EmployeeFact,
    IF (
        EmployeeFact[End] <= [SelectedDate]
            && EmployeeFact[End] > [SelectedDate (Start)],
        EmployeeFact[Employee] & " ("
            & ROUNDDOWN ( [Employee average age], 0 ) & ") "
    )
)

I then combined them in another “Harvester” measure:

Selected Employee List =
SWITCH (
    [Selected Slicer],
    1, [Employee list],
    2, [Joiner list],
    3, [Leaver list]
)

I’ve split the average age into age band groupings.

Under 20 =
CALCULATE (
    [Selected Headcount Measure],
    FILTER ( EmployeeFact, [Employee average age] < 20 )
)

20 to 29 =
CALCULATE (
    [Selected Headcount Measure],
    FILTER (
        EmployeeFact,
        20 <= [Employee average age]
            && [Employee average age] < 30
    )
)

30 to 39 =
CALCULATE (
    [Selected Headcount Measure],
    FILTER (
        EmployeeFact,
        30 <= [Employee average age]
            && [Employee average age] < 40
    )
)

40 to 49 =
CALCULATE (
    [Selected Headcount Measure],
    FILTER (
        EmployeeFact,
        40 <= [Employee average age]
            && [Employee average age] < 50
    )
)

…and so on.

Then put each of these measures on the values section of the funnel chart.

I did a similar form of banding for tenures:

(Please note that in order to economize on the real estate space on the dashboard, I’ve used short-hand expressions in the tenure measures and just stated “2 – 5 years” instead of properly stating “2 to less than 5 years” and so on.)

< 1 year =
CALCULATE (
    [Selected Headcount Measure],
    FILTER ( EmployeeFact, [Employee tenure] < 1 )
)

1 – 2 years =
CALCULATE (
    [Selected Headcount Measure],
    FILTER ( EmployeeFact, [Employee tenure] < 2 && [Employee tenure] >= 1 )
)

2 – 5 years =
CALCULATE (
    [Selected Headcount Measure],
    FILTER ( EmployeeFact, [Employee tenure] < 5 && [Employee tenure] >= 2 )
)

5 – 10 years =
CALCULATE (
    [Selected Headcount Measure],
    FILTER ( EmployeeFact, [Employee tenure] < 10 && [Employee tenure] >= 5 )
)

> 10 years =
CALCULATE (
[Selected Headcount Measure],
    FILTER ( EmployeeFact, [Employee tenure] >= 10 )
)

I then included each of these measures on the values section of the clustered column chart.

It’s worth noting that while all the measures previously discussed such as the headcount, list of names (with age in brackets), average age and tenure profile are all snapshots as of the selected date, the joiners and leavers measures sum the total number of joiners and leavers during the period of the two selected time points. If only one period is selected, that months’ joiners and leavers are calculated (from the beginning of the month to the end of the month.)

The report can be sliced, diced, and chopped by various criteria like gender, role, time points, department, location, and more. I also want to point out that the information packed into this interactive, one-page Power BI headcount dashboard is equivalent to a PDF document of over 10,000 pages. Let’s be honest, I can’t stand PDF reports. With Power BI, you can apply all kinds of slicing and dicing to the visualizations without the hassle of scrolling through endless pages in a bulky document to find the information you need. It really shows how fluid, agile, and smart Power BI is when it comes to data visualization.

Since I first implemented the headcount dashboard many years ago, I’ve reused this DAX recipe in several different organizations. It’s helped me master headcount and data across various organizations, which has been incredibly useful for tasks like fixed cost analysis, budgeting, and forecasting.

These are just a few examples of what you can do with PowerPivot using the employee database—things that used to require specialized software. Thanks to PowerPivot’s ability to analyze the employee database with a time dimension continuum (using a disconnected table), we can easily turn the clock backward🕰️ (and forward too!) to get an analytical snapshot of headcount demographics, along with details about joiners and leavers for any selected period.

I have attached my Power BI headcount dashboard pbix file.

Enjoy slicing & dicing the interactive report!

Headcount Dashboard.pbix