Treat each month as a controlled intake
I applied deterministic file precedence and recorded source provenance for every reporting cycle.
Operational Reporting · Data Quality
NHS Workforce Capacity & Service Delivery Reporting
A repeatable monthly reporting process for workforce, availability, demand, and delivery performance.
Role
Data Analyst · Analytics Engineer
Tools
Power Query · Excel · Power BI
Scope
April 2024–March 2025
Cohort
20 London providers
Outcome
Repeatable reporting cycle
My approach
Keep exceptions visible
I classified rows as Accepted, Review Required, or Rejected and reconciled totals across sources.
Serve governed management views
I used a KPI dictionary, star schema, Excel PivotTables, and Power BI views for repeatable use.
The problem
Monthly reporting must control change.
A new file can contain revised, duplicate, or invalid records. Managers still need consistent KPI outputs.
Input control
Select the correct file version and preserve source provenance.
Data quality
Classify records as Accepted, Review Required, or Rejected.
Decision use
Give non-technical managers a stable operational view.
How the system works
Repeatable reporting, not one-time analysis.
Each reporting period enters the same controlled path.
01Monthly files
↓
02Controlled intake
↓
03Validation and classification
↓
04Reconciliation
↓
05Reporting model
↓
06Excel and Power BI
Decision evidence
What needs management attention?
Three views move from portfolio status to service pressure and then to reporting trust.
01 · Executive overview
Where is performance off track?
Why this visual exists. Workforce, demand, delivery, pressure, and provider alerts are visible in one management view.
Finding
March demand rose 12.6% from February while four-hour performance was 76.5%, 1.5 percentage points below the reference.
Interpretation
The headline connects rising activity to delivery pressure, while the alert table identifies providers for review.
Decision
Move to provider-level demand and long-wait analysis before assigning operational action.
02 · Demand and delivery
Which providers need review?
Why this visual exists. Demand, admissions, four-hour breaches, and 12-hour waits are shown together so volume and service failure are not confused.
Finding
Lewisham and Greenwich has the lowest four-hour performance at 65.7%. Barts has the largest over-four-hour count in the watchlist at 13,067.
Interpretation
A low rate and a high breach count describe different operational problems.
Decision
Review rate, volume, and long waits together before prioritising provider support.
03 · Data quality
Can managers trust the refresh?
Why this visual exists. File status, record classification, reconciliation, and exceptions make the reporting boundary visible.
Finding
The refresh processed 25 files, flagged 44,044 records, and recorded zero unexpected rejections or revised periods.
Interpretation
A 66.4% acceptance rate is not hidden. Review-required records remain traceable by dataset and exception type.
Decision
Publish the governed view while routing unmatched organisation codes to review.
Source: original Power BI screenshots from the project repository. Public aggregate data; not a live NHS production deployment.
Related concepts
Explore the controls behind the report.
Trust
Data trust
Explore concept →Modelling
Analytics modelling
Explore concept →Operations
Pipeline operations
Explore concept →Metrics
Measurement design
Explore concept →Keep invalid and uncertain records separate.
The transformation rejects broken records, routes uncertain schema changes to review, and accepts only records that pass the controls.
View implementation on GitHub ↗
if List.Count(MissingColumns) > 0 then
"Rejected"
else if [ReportingMonth] = null or [MeasureValue] < 0 then
"Rejected"
else if List.Count(UnexpectedColumns) > 0 then
"Review Required"
else
"Accepted"