Project overview
An analytical solution designed to monitor workforce performance, headcount, employee attrition, recruitment and absenteeism through advanced data modelling and DAX calculations. Built as an independent portfolio project on a synthetic HR dataset, it deliberately uses role-playing dimensions and inactive relationships — the patterns that separate intermediate models from advanced ones.
The business problem
People questions are period questions — headcount at a point in time, attrition over a rolling year, time-to-hire per quarter — but HR extracts are usually point-in-time snapshots. Joining them incorrectly double-counts employees or misses movement between departments. HR teams also need the same numbers cut by manager, location and job family, which generic templates rarely handle. The project builds a model where those period-based people metrics stay correct under any filter.
Business objectives
Dataset description
The dataset is synthetic, generated to resemble an HR system extract from a company of roughly one thousand employees across several years: monthly headcount snapshots, an employee master with job history, termination records with reasons, recruitment pipeline events, and absence records. Event facts are keyed to both the employee and the effective date — which is exactly what forces proper period-based modelling.
FactHeadcount
Monthly snapshot of active employees by department, location and job family
FactEmployeeEvents
Hires, terminations, promotions and transfers with effective dates
FactRecruitment
Application-to-hire pipeline events with stage, source and durations
FactAbsence
Absence records with type, duration and workdays lost
DimEmployee / DimDate
Employee master plus a date table driving all period comparisons
Data preparation methodology
Reconcile snapshots against events
Validated that monthly headcount snapshots tie out to hires minus leavers from the event table — the classic HR reconciliation.
Model employee movement
Captured department and manager changes as dated events rather than overwriting the employee master.
Standardise termination reasons
Mapped free-text exit reasons into a controlled hierarchy for voluntary, involuntary and retirement splits.
Design for confidentiality
Applied aggregation-safe measure design so small-group filtering behaves sensibly — the same discipline row-level security requires in production.
Automate period logic
Built a date table with ISO years, fiscal periods and rolling windows that every time-based measure reuses.
Data model
Data model diagram — placeholder
A diagram showing the employee event and headcount facts against the employee dimension and the role-playing date dimensions will be added here.
Report preview
Report page screenshot — placeholder
Report page screenshot — placeholder
Screenshots of each report page — workforce overview, attrition analysis, recruitment funnel and absence insights — will be added here.
Key KPIs & business metrics
Headcount (point in time)
Employees active as of any selected date, by department, location and job family
Attrition rate
Leavers as a share of average headcount over a rolling 12 months, split voluntary and involuntary
Average tenure
Mean length of service for current employees and for leavers, to spot tenure-driven risk
Time to hire
Median days from requisition approval to start date, by role family and source
Absence rate
Workdays lost as a share of available workdays, by team and absence type
Offer acceptance rate
Accepted offers as a share of extended offers, segmented by source
DAX & technical implementation
The model's defining pattern is role-playing dates: hire, termination and absence dates each need their own relationship to the calendar. Only one can be active, so the key measures switch context explicitly with USERELATIONSHIP inside CALCULATE.
Leavers (termination-date context)
Counts leavers through the inactive termination-date relationship instead of the default hire-date join.
Leavers =
CALCULATE (
DISTINCTCOUNT ( FactEmployeeEvents[EmployeeKey] ),
FactEmployeeEvents[EventType] = "Termination",
USERELATIONSHIP ( DimDate[Date], DimDate[TerminationDate] )
)Attrition rate (rolling 12M)
Annualises attrition against average headcount over the trailing year, regardless of slicer selection.
Attrition Rate R12M =
VAR Leavers12M =
CALCULATE (
[Leavers],
DATESINPERIOD ( DimDate[Date], MAX ( DimDate[Date] ), -12, MONTH )
)
VAR AvgHeadcount =
AVERAGEX (
VALUES ( DimDate[YearMonth] ),
[Headcount EOM]
)
RETURN
DIVIDE ( Leavers12M, AvgHeadcount )Median time to hire
Median over requisitions — avoids the skew that averages introduce when a few searches run long.
Time to Hire (Median) =
MEDIANX (
VALUES ( FactRecruitment[RequisitionKey] ),
CALCULATE (
DATEDIFF (
MIN ( FactRecruitment[OpenedDate] ),
MAX ( FactRecruitment[StartDate] ),
DAY
)
)
)Technical challenges & solutions
Headcount snapshots disagreed with event-derived headcount.
Approach — Traced the gap to backdated transfers recorded only in snapshots; rebuilt the event table as the single source of truth and reconciled snapshots against it.
Multiple date relationships made simple measures return blank or wrong-period numbers.
Approach — Made hire-date the single active relationship and used USERELATIONSHIP explicitly in every other date-context measure, documented per measure.
Small departments let individual employees be inferred from KPI values.
Approach — Applied aggregation-safe patterns — the same discipline as dynamic row-level security — so fine-grained filters never expose single-person rows.
Key insights
The findings below describe patterns demonstrated in the synthetic dataset — they show the analytical questions the report answers, not results from a real client.
- 01Attrition concentrates in the first twelve months of tenure — visible when tenure bands are cut against the leaver population.
- 02Source quality predicts speed: referrals and internal moves fill requisitions faster than agency channels in the demo data.
- 03Absence and performance move together at team level, which the combined dashboard makes easy to spot.
- 04Manager-level drill-through reveals patterns that company-level averages flatten.
Source & repository
GitHub repository link — coming soon
A real repository URL will be linked here once published.
Interactive report
Interactive Power BI report — coming soon
The published Power BI report will be embedded here once available.
Project technologies
Want a build like this on your data?
Tell me what you are measuring and who needs to see it — I will come back with a scope, a timeline and a fixed quote.