Hospital Episode Statistics · England
NHS Inpatient
Analytics Platform
A complete intelligence layer built on 22.5M FCEs across 590 NHS providers. Business requirements, clinical insights, data profiling, and report design — all in one place.
0
Total FCEs
Finished Consultant Episodes
0
18m+ Backlog
Patients waiting longest
0
Emergency Rate
Unplanned admissions
0
DAX Measures
Across 7 folders
0
Bed Days
Total inpatient consumption
0
Avg Wait Days
SW outlier: 125.8 days
0
Providers
NHS organisations
0
Deprivation Ratio
Emergency rate inequality
Admission Type Breakdown
18.47M FAEs by pathway — FY 2024/25
Regional FCE Distribution
7 commissioning regions by volume
Elective Wait Band Profile
England — patients by wait duration
Task 7
Business Requirements Document
Comprehensive BRD built from the live Power BI model — 12 tables, 54 measures, 22 calculated columns, 7 relationships.
Model Architecture — Table Row Counts
Total 971,151 rows across 5 fact tables
Measures by Display Folder
54 DAX measures across 7 analytical folders
1.0 Overview
2.0 Context
3.0 Stakeholders
4.0 Scope
5.0 Data Model
6.0 Requirements
7.0 Non-Functional
Executive Summary
This BRD defines the analytical requirements for the NHS HES 2024–25 Inpatient Admissions Power BI solution. The model has been fully built, validated and deployed with all 54 DAX measures verified against the Analysis Services XMLA endpoint.
The solution covers 22,555,615 FCEs across 590 NHS providers providing decision-support analytics for Trust Board, Clinical Directors, Finance, Commissioning and Public Health teams.
Version
2.0
Post full model build
Data Period
2024/25
April 2024 – March 2025
Total Measures
54
Across 7 display folders
Calc Columns
22
Across 4 fact tables
Business Context & Objectives
The NHS faces three concurrent pressures: 72,587 patients waiting 18+ months; a deprivation-driven emergency gap of 311,183 additional admissions; and a day case rate of 38.4% against a target of 80%+.
| Ref | Strategic Objective |
|---|---|
| SO1 | Provide a single trusted source of NHS activity metrics for 2024/25 |
| SO2 | Enable CORE20PLUS5 equity reporting aligned to NHS England requirements |
| SO3 | Support the national elective recovery programme with granular backlog analysis |
| SO4 | Underpin provider benchmarking and pathway improvement initiatives |
Stakeholders & Audiences
- Trust Board / CEOStrategic oversight — national KPIs, trend headlines, equity position.
- Medical DirectorClinical governance — specialty pressure, LOS efficiency, diagnosis trends.
- Director of FinanceResource planning — bed day consumption, day case rates, benchmarking.
- Director of CommissioningContract management — regional comparisons, waiting list volumes.
- Public Health LeadPopulation health — IMD gradient, ethnicity equity, CORE20PLUS5.
- Information AnalystData operations — model structure, DAX logic, data quality.
Scope Definition
In Scope
HES Admitted Patient Care 2024/25 · England/Region/Provider level · IMD Deprivation · Ethnicity · Specialty demand · ICD-10 diagnoses · OPCS-4 procedures · Elective wait bands · Emergency vs elective profiling
Out of Scope
Outpatient / A&E attendance data · Mental health community MHSDS · Primary care activity · Mortality outcomes · Patient-level records (pre-aggregated)
Data Model Summary
| Type | Table | Detail |
|---|---|---|
| Fact | pla (Planning) | 66,323 rows · FCE/FAE/Bed Days — England, Region, Provider |
| Fact | proc (Procedures) | 369,250 rows · OPCS-4 surgical procedure codes |
| Fact | diag (Diagnoses) | 508,002 rows · ICD-10 diagnosis codes |
| Fact | oth (Other) | 27,969 rows · IMD, Ethnicity, Specialty breakdowns |
| Fact | provider-tab | 607 rows · Provider-level wide format |
| Dimension | Calendar | 2,557 rows · Apr 2019–Mar 2026 · Marked as Date Table |
| Bridge | _NHS_Year_Lookup | 7 rows · Unique NHS Year key · Bridges Calendar → pla |
| Hidden Ref ×4 | _Specialty_Ref / _Diagnosis_Ref / _IMD_Bridge / _Ethnicity_Groups | Reference lookup tables — hidden in Model view |
Functional Requirements
| ID | Requirement | Priority | Key Measures |
|---|---|---|---|
| FR01 | National KPI dashboard with FCEs, FAEs, bed days, emergency rate, day case rate | Critical | Total FCEs, Emergency Rate %, Day Case Rate % |
| FR02 | Regional benchmarking map with activity, wait and backlog toggle | Critical | Avg Elective Wait Days, Waiting 18 Months Plus |
| FR03 | Deprivation gradient across 10 IMD deciles | Critical | Deprivation Emergency Ratio, Deprivation Bed Day Gap |
| FR04 | Elective backlog tracker with all six wait bands | Critical | Backlog Severity Score, 18 Month Plus % of Backlog |
| FR05 | Age and gender demographic profile | High | Male/Female FCEs, Elderly %, Children FCEs |
| FR06 | Specialty demand heatmap (FCE × age band) | High | via oth MAINSPEF + _Specialty_Ref |
| FR07 | Ethnicity equity dashboard — CORE20PLUS5 | High | Ethnicity Missing Rate %, Ethnic Group Broad |
| FR08 | Clinical diagnosis chapter breakdown — ICD-10 | High | Cancer, Respiratory, Circulatory, Mental Health measures |
| FR09 | Provider benchmarking scatter (LOS vs emergency rate) | High | Bed Days per FCE, Emergency Admission Index |
| FR10 | Data quality indicators and ethnicity completeness | Medium | Ethnicity Missing Rate % (13.0%) |
Non-Functional Requirements
| Category | Requirement | Standard |
|---|---|---|
| Performance | All 54 measures return results | Under 3 seconds — verified on standard desktop |
| Data Refresh | Model refresh cadence | Manual on receipt of updated NHS Digital annual extract |
| Compatibility | Platform support | Power BI Desktop March 2026+ and Power BI Service |
| Security | Row-level security | Configure per organisational requirements before Service publication |
| Data Quality | NHS suppression (*) handling | All (*) values replaced with null — verified across proc, diag, pla |
| Accessibility | Report accessibility | Alt text on visuals, WCAG 2.1 AA colour contrast |
Task 8
Top 10 Business Insights
All figures verified live against the Power BI model via XMLA DAX queries. Every number is a direct measure result from your 22.5M FCE dataset.
Regional Wait Times Comparison
Mean elective wait in days — South West crisis visible
Deprivation Gradient — Emergency Rate by IMD Decile
Emergency admissions per decile (most → least deprived)
ICD-10 Disease Chapter Admissions
Top 8 diagnosis chapters by FAE volume
Gender Split — FCE Distribution
Male vs Female admissions breakdown
Age Band FCE Distribution
FCE volume by age group — 75+ dominates
01
South West is a System Failure, Not a Pressure
The South West holds 31,415 of England's 72,587 patients waiting 18+ months — 43% of the national backlog concentrated in a region with only 10.6% of FCE volume. Mean wait of 125.8 days is 90% longer than London's 66.3 days.
⚠ SW Wait: 125.8d vs England avg: 83.6d
02
Deprivation is the Strongest Emergency Predictor
Most deprived 10% generate 832,523 emergency admissions vs 521,340 in least deprived — a ratio of 1.60. They also consume 1,921,388 more bed days. Deprived patients admitted more and stay longer, compounding bed pressure by 54%.
Ratio 1.60 | Gap: 311,183 admissions | 54% more bed days
03
Day Case Rate at Half NHS Target — Largest Efficiency Lever
England's day case rate is 38.4% vs the NHS 80%+ target. Closing half the gap would convert ~4.7M overnight episodes to day cases, releasing an estimated 22M bed days — equivalent to several district general hospitals.
Current: 38.4% | Target: 80%+ | Gap: 41.6 pp
04
Cancer & Digestive: Joint Largest Disease Burden
Cancer (C00–D48) accounts for 2,517,314 FAEs and digestive (K00–K93) for 2,513,714 — together 27.3% of all admissions. Both exceed respiratory + circulatory combined. Direct theatre and bed capacity planning implications.
Cancer: 2.52M | Digestive: 2.51M | Combined: 27.3%
05
40% of FCEs Have No Procedure — The Invisible Burden
9,017,535 FCEs carry no surgical procedure code. This medical admission cohort drives the dramatic gap between mean LOS (4.69 days) and median LOS (1 day) — these patients disproportionately occupy acute beds.
No Procedure FCEs: 9.02M (40%) | Mean LOS: 4.69d vs Median: 1d
06
Female Admissions 21% Higher — Clinically Concentrated
12,282,856 female FCEs vs 10,125,067 male (54.8% female). The entire excess concentrates in maternity (1,317,464 O00–O99) and the 75+ age bands where women outnumber men due to greater longevity.
Female: 12.28M | Male: 10.13M | Maternity: 1.32M
07
Waiting List 2.78× Planned Admissions — Structural Undercapacity
7,322,118 waiting list admissions against 2,638,492 planned — Elective Demand Ratio of 2.78. The system runs on reactive throughput. Backlog Severity Score of 0.151 means early intervention is still viable.
Demand Ratio: 2.78 | WL: 7.32M vs Planned: 2.64M
08
Elderly 75+ = 27.5% of FCEs, Higher Share of Bed Days
6,211,987 FCEs for patients aged 75+ (27.5% of total). This cohort has significantly longer LOS than younger groups — their bed day share substantially exceeds their FCE share. The 75–79 band alone is the single largest age cohort.
Elderly FCEs: 6.21M (27.5%) | Peak: 75–79 at 2.28M
09
13% Ethnicity Gap — CORE20PLUS5 Compliance Risk
13.0% of FCE records have ethnicity coded as Not Stated or Not Known (~2.93M FCEs). This directly limits the ability to measure ethnic health inequalities required under the NHS CORE20PLUS5 framework.
Missing Ethnicity: 13.0% (~2.93M FCEs)
10
Mental Health Inpatient Demand Under-Represented
135,157 FAEs under F00–F99 (Mental & Behavioural Disorders) — just 0.7% of all admissions. NHS Mental Health Trusts are largely outside HES acute data. MHSDS supplementary data required for complete system analysis.
Mental Health FAEs: 135,157 (0.7%) | MHSDS needed
Task 9
Data Profiling Analysis
Complete statistical profiling of 971,151 rows across 5 fact tables. NHS suppression (*) resolved. All data quality flags documented with live model verification.
Deprivation Gradient — FCE & Bed Day Burden by IMD Decile
Most deprived → least deprived (left to right) · Both metrics show clear gradient
Wait Time Distribution — Skew Analysis
Mean 83.6 days vs Median 34 days reveals extreme right skew
Ethnicity Data Completeness
13% missing rate — CORE20PLUS5 risk
Regional 18m+ Backlog Distribution
South West holds 43% of national 18m+ patients
Elective vs Emergency Split
Admission pathway composition — England
pla — Planning
Rows66,323
Total columns17 (10+7 calc)
Suppressed fixed~200 → null
Null rate<1%
Status✓ Clean
proc — Procedures
Rows369,250
Total columns8 (5+3 calc)
Suppressed fixed143 → null
Null rate0.04%
Status✓ Clean
diag — Diagnoses
Rows508,002
Total columns10 (5+5 calc)
Suppressed fixed587 → null
Null rate0.12%
Status✓ Clean
oth — Other
Rows27,969
Total columns12 (5+7 calc)
SuppressedNone
Null rate0%
Status✓ Clean
provider-tab
Rows607
Columns50 (Column1–50)
Suppressed (*)~15% of cells
HeadersNot normalised
Status⚠ Needs reshape
Calendar & Bridge
Calendar rows2,557
Calendar columns18
NHS Year Lookup7 unique rows
Marked as Date Table✓ Yes
Status✓ Complete
Core Activity
Total FCEs22,555,615
Total FAEs18,468,856
FCE:FAE Ratio1.22
Total Bed Days48,360,197
Day Case FCEs8,661,338
Day Case Rate38.4%
Wait & LOS Stats
Avg Elective Wait83.6 days
Median Elective Wait34 days
Mean:Median Ratio2.46× skew
Avg LOS4.69 days
Median LOS1 day
LOS Mean:Median4.69× skew
Backlog
Under 1 month2,857,425
1–3 months1,730,742
3–6 months728,786
6–12 months528,533
12–18 months178,489
18 months+72,587
Data Completeness by Dimension
Coverage rates across key analytical dimensions
Completeness Detail
Ethnicity — Known87.0%
Ethnicity — Not Stated8.7% (1.97M)
Ethnicity — Not Known4.3% (0.96M)
Gender — Known99.3%
Gender — Unknown0.7% (147K)
Age — Complete100.0%
IMD — Assigned~97.6%
Procedure — Present60.0% (medical expected)
| Region | FCEs | Avg Wait (days) | Emergency | 18m+ Backlog | Bed Days |
|---|---|---|---|---|---|
| Midlands | 4,458,065 | 80.9 | 1,403,215 | 9,160 | — |
| NE & Yorkshire | 3,671,825 | 77.4 | 1,073,475 | 5,955 | — |
| London | 3,369,800 | 66.3 | 813,520 | 6,025 | 7,351,270 |
| South East | 3,193,025 | 81.9 | 989,225 | 6,030 | — |
| North West | 3,016,805 | 79.4 | 888,975 | 8,370 | — |
| East of England | 2,460,895 | 87.6 | 710,715 | 5,620 | 4,708,587 |
| ⚠ South West | 2,385,115 | 125.8 | 735,855 | 31,415 | — |
Task 10
Report Design Recommendations
Six fully specified Power BI reports using the 54 DAX measures. Each includes visual specs, filter logic, DAX references and clinical insights to surface.
Report 1 Preview — Executive KPI Targets
Actual vs NHS target for each headline metric
Report 4 Preview — Backlog Wait Band Stack by Region
100% stacked — South West 18m+ share is stark
Report 5 Preview — ICD-10 Clinical Chapter Demand
FAE volume by disease chapter — Cancer & Digestive lead
Report 3 Preview — Deprivation Equity: FCE vs Bed Day Gap
Most vs least deprived 10% across 3 key metrics
Report 01
Executive Summary Dashboard
Trust Board · CEO · Medical Director
One-page strategic overview for monthly board pack. KPI cards with conditional formatting, bullet chart vs NHS targets. Default slicer locked to 2024/25.
- 5 KPI cards: Total FCEs, Emergency Rate %, Avg Wait Days, Waiting 18m+, Day Case Rate % — all with red/amber/green formatting
- Horizontal bullet chart: actual vs NHS target for each KPI
- Single large KPI: Waiting 18 Months Plus with red pulse indicator
- Calendar[NHS Year] slicer defaulted via _NHS_Year_Lookup[Is_Current_Year]
Key Measures
Key message: The 18m+ backlog figure with a red indicator. This is the number every board member will be asked about — surface it prominently.
Report 02
Regional Benchmarking
Director of Commissioning · Regional Teams
Compare all 7 commissioning regions on activity, waiting times and emergency pressure. Filled map as hero visual with toggle metric slicer.
- Filled map of England — colour = selected metric (FCE / Wait / Emergency / 18m+)
- Sorted bar chart: 7 regions on selected metric, reference line at England average
- Data label annotation on South West calling out 125.8-day wait explicitly
- Detail table: all wait bands by region (Under 1m → 18m+)
Key Measures
Filter: pla[ORG_LEVEL] = "REGION" and pla[MEASURE_TYPE] = "Summary" — prevents national totals from contaminating regional comparisons.
Report 03
Deprivation & Health Equity
Public Health Lead · CORE20PLUS5
Demonstrate NHS commitment to reducing health inequalities. IMD gradient as hero, deprivation ratio KPI, ethnicity breakdown with data quality warning.
- 10-bar chart ordered by IMD Sort Order — colour gradient dark red to NHS blue
- Toggle: Emergency Admissions / Bed Days / FCE count by IMD decile
- KPI card: Deprivation Emergency Ratio (1.60) with narrative
- Ethnicity FCE breakdown using oth[Ethnic Group Broad] calculated column
- Warning card: Ethnicity Missing Rate % (13.0%) flagged prominently
Key Measures
Key message: Every IMD decile step adds ~60,000 FCEs and ~200,000 bed days. This gradient IS the equity gap.
Report 04
Elective Backlog Tracker
Operations · RTT Team · Recovery Board
Monitor the elective waiting list and recovery trajectory. 100% stacked bar chart by region with all wait bands. What-If parameter for scenario modelling.
- 100% stacked bar: 7 wait bands by region — South West 18m+ slice dramatically visible
- Table: 7 regions ranked by Waiting 18m+ with conditional red formatting
- What-If parameter: "If capacity increases by X%" → clearance projection
- 5 KPI cards: 18m+, Backlog 6m+, 18m+ % of Backlog, Severity Score, Avg Wait
Key Measures
DAX What-If: [Waiting 18 Months Plus] / (1 + WhatIfParam/100) — residual backlog under capacity increase scenarios.
Report 05
Clinical Demand Analysis
Medical Director · Clinical Directors
Understand specialty and disease burden for workforce and capacity planning. Matrix heatmap and ICD-10 chapter breakdown.
- Matrix heatmap: top 15 specialties (from _Specialty_Ref) × age bands — FCE count, blue scale
- Bar chart: ICD-10 chapter admissions — Cancer, Digestive, Respiratory, Circulatory, MSK, MH
- KPI card: No Procedure FCEs (9,017,535) labelled "Medical admissions (no procedure)"
- Scatter: LOS vs Emergency Rate by specialty using oth[Is Specialty Row] filter
Key Measures
Data quality: Mental health (135,157 = 0.7%) is structurally under-counted — flag this clearly on all mental health visuals.
Report 06
Provider Benchmarking
Director of Finance · Performance Team
Identify outlier providers for investigation or good practice spread. Bubble scatter with 590 providers. Quadrant analysis based on England means.
- Bubble scatter: X=Avg LOS, Y=Emergency Rate %, size=FCE volume — 590 providers
- Reference lines at England mean LOS (4.69d) and emergency rate (35.8%)
- Quadrant labels: High LOS+High Emergency (red flag) vs Efficient+Elective (exemplar)
- Tooltip: provider name, FCE count, wait time, day case rate on hover
- Click bubble → filters detail table showing all metrics for that provider
Key Measures
Filter: pla[Is Provider Level] = TRUE on ALL page visuals — prevents national aggregates from appearing alongside provider data.
Design System
Cross-Report Principles
Standards that apply across all six reports for consistency, accessibility and clinical accuracy.
Colour Standards
NHS Blue (#005EB8) for volume · Amber (#FFC000) for efficiency · Red (#DA291C) for breaches. Colour-blind safe palettes on equity pages.
Calendar Slicer
Use Calendar[NHS Year] on every page — establishes the pattern for multi-year data even with single-year current data.
Measure Category Slicer
Use pla[Measure Category] calculated column as a slicer to filter data by analytical domain without needing raw MEASURE codes.
Data Quality Footnote
Every page: "Ethnicity: 13.0% unknown. NHS suppressed values (*) excluded from all calculations."
RLS Before Publishing
Row-level security must be configured per organisational access requirements before publishing to Power BI Service.
Accessibility
Alt text on all visuals. Tab order set logically. WCAG 2.1 AA contrast ratios. Screen reader tested before publication.