Counting people is harder than it looks

“How many people work here?” sounds like the simplest question an HR report can be asked. It is one of the easiest to get wrong, because every wrong way of answering it produces a perfectly reasonable-looking number.

I rebuilt five years of an organisation’s HR records as a Power BI model — 3,000 people, 1,533 of whom have left — with headcount, joiners and leavers, turnover, engagement and training. The data is a public, synthetic dataset, which is useful: it contains every mistake a real HR estate does, and I can show you all of them.

1. A status is not an event

Every employee record has an employment status and a start and exit date. They disagree 1,146 times. 991 people are marked “Active” with an exit date that has already passed. All 69 “Future Start” employees started years ago and have since left. Everyone on leave of absence has an exit date too.

Count headcount off the status field — which is what the field is for — and the file says 2,613 people work here. Count it off the dates and the last month-end, July 2023, has 1,480. The status version is 77% too high, and nothing about it looks wrong: it is a clean integer, from a clean column, with no nulls in it.

A status is a label someone has to remember to update. An exit date is an event.

The model derives employed or left from the dates and keeps the HR system’s status beside it, with a flag wherever the two disagree. Believing the event is the right default; hiding the disagreement is not, because the disagreement is itself a finding about the process that feeds the system.

2. Headcount is a stock, turnover is a flow

Joiners and leavers are flows: they happen on a date and they add up. Headcount is a stock: it is true at a moment and it does not. Sum headcount across twelve month-ends and you have counted everyone twelve times.

So the model keeps two different kinds of table. One records each joining and leaving as an event on its date. The other is a snapshot — one row per person per month-end they were employed at, 68,587 rows in all — and the headcount measure reads the last month-end in view and never sums across them. Average headcount is the mean of the month-ends, and a month in which a small department had nobody counts as zero rather than being skipped, which would quietly flatter the average.

Turnover is where the two meet: leavers, a flow, divided by average headcount, a stock. Get either half wrong and the rate is wrong.

3. A year is not always twelve months

The file starts in August 2018 and ends in August 2023. So 2018 holds five months and 2023 holds seven, and a turnover rate computed over the calendar year treats both as if they were twelve.

Year	Months	Joiners	Leavers	Headcount, year end	Turnover, annualised
2018	5	255	4	251	6.7%
2019	12	598	62	787	11.4%
2020	12	592	133	1,246	12.8%
2021	12	600	278	1,568	19.2%
2022	12	620	460	1,728	27.4%
2023	7	335	596	1,480	62.8%

Annualised over the seven months it has, 2023’s turnover is 62.8%. Divide by twelve instead and it would look like a year that had calmed down. It is the year leavers overtook joiners for the first time: headcount peaked at 1,728 in December 2022 and had fallen to 1,480 by July.

Where the leaving happens

With the counting straight, the pattern is clear. 725 of the 1,533 leavers — 47% — went inside their first year, and the median leaver had served 1.07 years. Turnover that front-loaded is a question about hiring and onboarding before it is a question about retention, and it points at a different budget and a different owner.

Two files that do not belong to the people they describe

The engagement survey and the training records link to employees by ID, and the link holds for all 3,000. The dates do not. 1,338 survey responses and 1,317 training records are dated before the person started or after they left — and those training records carry $735,145 of the $1,675,886 recorded, 44% of the spend. The model flags every one, reports on the in-employment records, and shows the naive figure beside the real one.

The recruitment file is worse, and more instructive. Its applicant IDs run from 1001 to 4000 — exactly the range of the employee IDs — so it joins to the employee table perfectly. On a shared ID, not one of 3,000 names matches. None of the applicants applied for a job title that exists in the company. A shared key range is a coincidence, not a relationship; the file was tested, recorded as unrelated, and left out.

A headcount is only as good as the rule that decides who counts — and that rule has to be written down, tested and visible in the report.

The same three rules apply to any population a report has to count: customers, accounts, policies, cases. Each has a status field someone forgets to update, a stock that should never be summed, and a first or last period that is shorter than it looks.