Skip to content
Tutorials Center
My learning

Topics

Lesson 50 of 56 ยท Projects

Project: HR and Headcount Dashboard

Build an HR dashboard in Power BI: a monthly headcount snapshot table, hires, leavers and attrition measures, slicers and row-level security, checked in pandas.

HR data looks easy until you try to answer "how many people did we have in March?". An employee table only knows the current state: who is here now, when they joined and, perhaps, when they left. A report that counts rows in that table gives you today's headcount for every month. To show headcount over time, you need a snapshot of the workforce at the end of each month, and to report attrition you need to count leavers against an average headcount, not against today's.

In this project you will build the model for a headcount and attrition dashboard: an employee table, a monthly snapshot fact table, a date table with two date roles (hire and leave), the measures, a few slicers, and row-level security so that each department manager sees only their own team. Power BI Desktop is a GUI tool, so the steps are described and the DAX is read-only. The runnable examples are pandas stand-ins in your browser that compute the same numbers, which is also how you should verify the finished report.

HR data is personal data. The example uses employee IDs and departments only. In a real report, keep salary, health and performance fields out of the model unless they are needed, and protect them with the security features described below and in Row-Level Security.

The questions

What the dashboard answers

Headcount How many people at each month end?
Hires Who joined in the period?
Leavers Who left in the period?
Attrition Leavers against average headcount.
1 / 2

Why a snapshot table

Headcount is a stock, like a bank balance: it exists at a point in time, and you cannot add the balances of several days together. Hires and leavers are flows, events that happen in a period and can be added. Flows can be counted straight from the employee table using the hire and leave dates. A stock is easiest to report from a periodic snapshot fact table: one row per employee for each month-end on which the person was employed.

The grain of the snapshot is one employee at one month-end, with these columns: Month End (a date), EmployeeID, DepartmentKey and, if you need them, job level or location at that date. The department is stored in the snapshot because people move between teams, and last March's headcount for Sales must use last March's department, not today's.

You can build the snapshot in Power Query: generate the month-end dates, cross each with the employees, and keep the rows where the person was employed (hired on or before that date, and not yet left). In a production system the snapshot is usually produced by the data warehouse, one load per month. The stand-in below creates a small one with pandas, using the same rule: employed means hire_date <= month_end and (no leave date or leave_date > month_end).

Python Stand-in: build the monthly snapshot

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Each month-end has a different number of rows, and the June snapshot split by department adds up to the June headcount. In the real model you would check exactly that: the sum of the departments equals the total.

The model

Relate the tables like this:

  • Departments[DepartmentKey] one-to-many to Snapshot[DepartmentKey] and to Employees[DepartmentKey].
  • 'Date'[Date] one-to-many to Snapshot[Month End]. This is the active relationship, so a month slicer filters headcount.
  • 'Date'[Date] one-to-many to Employees[Hire Date] as the active relationship for hires, and to Employees[Leave Date] as an inactive relationship used by one measure.

An employee table has two dates and a date table can have only one active relationship to it, so the second one is inactive, and a measure switches it on with USERELATIONSHIP. This is the role-playing date pattern from Relationships, Cardinality and Filter Direction. Build the date table as in Date Tables and Time Intelligence Setup, including a Year Month text column such as 2026-03, sorted by a numeric Year Month Number.

The measures

DAX
Headcount =
CALCULATE (
    DISTINCTCOUNT ( Snapshot[EmployeeID] ),
    LASTDATE ( 'Date'[Date] )
)

Hires = COUNTROWS ( Employees )

Leavers =
CALCULATE (
    COUNTROWS ( Employees ),
    USERELATIONSHIP ( Employees[Leave Date], 'Date'[Date] )
)

Average Headcount =
AVERAGEX ( VALUES ( 'Date'[Year Month] ), [Headcount] )

Attrition % = DIVIDE ( [Leavers], [Average Headcount] )

Here is how each one works:

  • Headcount counts distinct employees in the snapshot, but only for the last date in the current filter context. For a month, that is the month-end; for a quarter or a year, it is the closing headcount of the period. This is the standard way to handle a stock (the details of CALCULATE are in CALCULATE and Filter Context). LASTDATE works because every snapshot date is the last day of a month, and the date table contains those days.
  • Hires counts employees using the active relationship on the hire date, so the date slicer selects people who joined in that period.
  • Leavers counts the same table through the inactive leave-date relationship. The rows with a blank leave date have no date to match, so they are never counted.
  • Average Headcount takes the headcount at each month in the selected period and averages them. With the month as the grain, a single month returns its own headcount.
  • Attrition % divides leavers by average headcount, with DIVIDE protecting against a blank denominator.

Because Hires counts rows of Employees, a department slicer must filter that table as well as the snapshot, so check that the filter direction runs from Departments to both tables.

Check them before you trust them

Calculate the same four numbers in pandas, month by month. The stand-in computes headcount at each month-end, hires and leavers inside each month, and the attrition for the whole period.

Python Stand-in: hires, leavers and attrition

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The last table shows the roll-forward check: opening headcount plus hires minus leavers must equal the closing headcount. Here it holds for every month after January (the opening for January is not in the data). A mismatch means a person has a hire date after their leave date, a missing date or a snapshot that disagrees with the employee table.

Notice the size of the attrition figure: five leavers on an average of about ten people is 48 percent over six months. That is a toy company. In a real report, always say what the period is. A six-month figure is not comparable with an annual one, and doubling it, as the last line does, is only an approximation. For a rolling twelve-month rate, divide the leavers of the last twelve months by the average headcount of the same months.

The report pages

A dashboard needs a few pages, and each page needs a clear question.

  1. Overview. Cards for headcount, hires, leavers and attrition %; a line chart of headcount by Year Month; a clustered column chart of hires and leavers by month. Add a department slicer, a date slicer and a single sentence as the title of each chart ("Headcount fell in June").
  2. Departments. A matrix with department in rows and months in columns, showing headcount, and a bar chart of attrition % by department. Use conditional formatting as in Conditional Formatting, KPIs and Cards to flag a department with attrition above a threshold. Small departments swing wildly (one leaver in a team of two is 50 percent), so consider showing the leaver count beside the percentage.
  3. Detail. A table of the leavers with their department, hire and leave dates, tenure and (only for authorised readers) the reason. Put it behind a drill-through page, see Drill-through, Tooltips, Bookmarks and Buttons.

Use Filters, Slicers and Sync to keep the date and department slicers in step across pages. Use a relative date slicer, or default the date slicer to the latest closed month, so the report does not open on a partial month.

Row-level security

An HR dashboard should not show every team to every manager. Row-level security (RLS) restricts which rows a user can read, based on a role and a DAX filter. The simplest design stores each department's manager email on the Departments table. A role named Manager filters it like this:

DAX
[Manager Email] = USERPRINCIPALNAME ()

Because Departments filters Employees and Snapshot through the relationships, one rule on the dimension restricts every table, and the measures need no changes. Create the role in Power BI Desktop with Manage roles, test it with View as, then publish and assign members to the role in the Power BI Service. A second role, for HR staff who may see everything, simply has no filter. A user who is in two roles gets the union of both. Workspace members with an Admin, Member or Contributor role bypass RLS when they use the model, so share the report through an app or the Viewer role instead (see Row-Level Security and Governance, Deployment Pipelines and Best Practices).

The stand-in below mimics the filter. It applies the manager rule to the June snapshot for three users and prints what each would see.

Python Stand-in: a row-level security filter

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

A user whose email matches no department sees nothing, which is the safe default: RLS removes rows, so an unmatched user gets an empty report instead of an error. In the real service, the rule is evaluated by the engine on every query, not by the report, so a user cannot get around it by editing visuals.

Also decide what must be hidden entirely. Object-level security can hide a column such as salary from a role, and a sensitivity label (see Governance, Deployment Pipelines and Best Practices) marks the report as confidential.

Final checks

  • Total headcount for each month equals the sum over departments.
  • Opening plus hires minus leavers equals closing, for every month.
  • Open View as for a manager and confirm that the numbers equal the pandas result for their department.
  • The report opens on the latest closed month and the title names the period.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why does headcount over time need a snapshot table?

    A stock such as headcount must be recorded at each point in time. The employee table shows who is employed now, so counting its rows for a past month gives the wrong answer.
  2. 2. How does the Leavers measure use the leave date when the Date table's active relationship is on the hire date?

    A role-playing date uses one active relationship and inactive ones. USERELATIONSHIP switches an inactive relationship on for the duration of one CALCULATE.
  3. 3. What is the roll-forward check for headcount?

    People enter through hires and exit through leavers, so opening plus hires minus leavers must equal closing. A mismatch points to bad dates or an inconsistent snapshot.
  4. 4. A manager's role filters Departments with [Manager Email] = USERPRINCIPALNAME(). Why do the Snapshot and Employees tables also become restricted?

    A filter on a dimension table flows to the related fact tables along the relationships, so one rule on Departments restricts all the data for that department.
  5. 5. Why is a six-month attrition figure not comparable with an annual one?

    A rate depends on the period it covers. State the period, or use a rolling twelve-month window so reports are comparable.

Summary

  • Headcount is a stock: store a monthly snapshot fact table (one row per employee per month-end) and read the closing value with CALCULATE and LASTDATE.
  • Hires and leavers are flows counted from the employee table; the leave date uses an inactive relationship activated with USERELATIONSHIP.
  • Attrition is leavers divided by average headcount for a stated period, protected with DIVIDE.
  • Verify with a roll-forward check (opening + hires - leavers = closing) and by recomputing in pandas.
  • Row-level security on the department dimension restricts every related table; test it with View as and keep sensitive columns out of the model or behind object-level security.

Next, keep practicing with Project: Customer Churn Analysis.