NHS A&E Service Performance Reporting Pack
A one page Power BI MI pack built the way a service reporting analyst works every month. It takes 24 months of NHS England A&E provider data (4,721 provider rows), tracks 4 hour performance against the 82% national target, 12 hour waits and month on month change, and ranks 120 major A&E trusts in a RAG table.
The problem
Monthly service reporting has to be accurate, comparable month to month and quick to refresh when the next file lands.
What I built
KPI cards
4 hour performance against the 82% target, attendances, 12 hour waits and month on month change.
RAG table
a DAX driven RAG status and worst 10 ranking across 120 major A&E trusts, with conditional formatting.
One click refresh
a Power Query folder append, so dropping in the new month and pressing Refresh moves the whole pack forward.
Reconciliation
every monthly file is checked in Python against NHS England's published totals.
Monthly commentary
a written summary of the September 2026 position.
How it works
Cleaning and reconciliation in Python
Combined and cleaned 24 months of provider level CSVs in pandas, then checked that every month adds up exactly to NHS England's own total row. That check caught a file where the total row was written differently, which would have doubled February.
KPIs in SQL
Wrote the KPIs in SQL (SQLite): 4 hour performance, attendances, 12 hour waits, and month on month change using window functions (LAG).
Power BI pack and refresh
The page shows KPI cards against the 82% target, trend lines, a RAG table of the 120 major A&E trusts and a worst 10 list using a DAX rank. Power Query reads the whole folder, so holding back September and then adding it showed that one Refresh moves the pack to the new month.
September 2026 commentary
Performance fell to 74.4%, 7.6 points below target. Only 7 of 120 trusts were on target, 12 hour waits rose by 5,631 to 50,582, and the North West had 4 of the 10 worst trusts.
Result
September 2026 at 74.4%, 7.6 points below the 82% target, with 7 of 120 trusts on target
"Built a monthly A&E MI pack from 24 months of NHS England data, reconciled every file to published totals, and reported September 2026 at 74.4% against the 82% target, with 12 hour waits up 5,631 to 50,582."