Workforce analytics
Five years of an organisation’s HR records rebuilt as a Power BI model: headcount, joiners and leavers, turnover, engagement and training. The HR system’s own status field disagrees with people’s start and exit dates more than a thousand times. The report believes the dates, and shows its working.
Headcount at each month-end
Turnover rose every year
Leavers over average headcount, annualised over the months each year actually holds: 2018 has five and 2023 seven, and dividing either by twelve would flatter it. Headcount is a stock, so it is read at month-ends and averaged, never summed.
| Year | Months | Joiners | Leavers | Headcount at year end | Average headcount | Turnover, annualised |
|---|
Rolling twelve-month turnover: leavers in the twelve months to each month-end, over the average headcount across the same months.
Nearly half leave in their first year
By department
Production is two thirds of the workforce, so it sets every company-wide rate. The small departments turn over faster, though at their size a handful of exits moves the rate.
| Department | Headcount | Leavers | Turnover | Left in year one |
|---|
Training nobody can place
The landing page: one year at a glance
The report opens on the page a people team asks for first, shown here as the report itself. Pick a year, and optionally a department, business unit or job role, and every tile, chart and title follows. For 2023 it reads 1,480 people at the end of July, 13.1% below July 2022, with 322 new hires from January to July against 352 and annualised turnover of 60.1% against 22.4%. Production holds 69% of the headcount, 56% of the workforce is female, and turnover is highest in the Executive Office, where five people make the rate jumpy. The rail down the left links to the five analysis pages.
- It cannot disagree with page 05. The tiles reuse the Year on Year page’s measures, so headcount, hires and turnover compare the same whole months of the year before. 2019 has no earlier year to compare with, and the tiles say so rather than showing a blank.
- A count is a stock, a hire is a flow. Headcount and every split of it are read at the year’s last month-end. Hires and turnover cover the months the year actually holds. The paragraph under the title says which is which.
- Icons in the house palette. Navy line work with one gold accent, drawn into each tile by DAX. Green and red are kept for better and worse, so the rise in turnover is the only red on the page.
- Titles that read the data. Each chart title is a measure, so it states what the chart shows under the current slicers.
- What the data cannot show is left out. The source has no attendance records, so there is no attendance tile. One state holds 88% of the people, so the split shown is business unit, not location.
- Checked by machine. Every figure on the page was compared with pandas in seven states, including each dropdown set and the year with nothing to compare: 579 checks, all matching.
Page 05: this year against the same months of another
Page 05 of the report puts one year beside an earlier one, and is shown here as the report itself rather than redrawn. From January to July 2023, against the same months of 2022, leavers rose from 216 to 570 while joiners fell from 352 to 322. Annualised turnover went from 22.4% to 60.1%, and headcount at the end of July was 1,480 against 1,704. Turnover rose in all six departments, and leavers rose in 22 of 25 divisions. The steepest department rise is in the Executive Office, where five people make the rate jumpy. The page says so.
- Only whole months are compared. 2023 has month-ends to July, so the comparison is January to July of the earlier year. That is why this page shows 60.1% turnover where the overview, which also counts leavers from the first six days of August, shows 62.8%. When the earlier year does not cover those months, as with 2018, the cards, charts and tables all say there is nothing to compare, and the summary says why.
- Red means worse, not lower. More leavers and higher turnover are red; more people and more joiners are green. The same rule drives the cards, the monthly columns and the bars in the tables.
- The cards are SVG, drawn by DAX. They show headcount with a ring against the earlier year, the share of leavers in their first year, turnover in both periods as two bars, and a bar for every division.
- A ranking that cannot tie. Two divisions shared both their change and their leaver count, so “the 8 divisions” first showed nine rows. Ties now go to the division with more leavers, then to the name.
- Checked by machine, not by eye. The page was mocked up in HTML with the real figures first. Its measures were then compared with pandas in six states, including one with a filter set in the panel: 1,046 checks, all matching.
