Referral-to-treatment waiting list semantic model

A waiting list report looks simple: one number, one percentage, a list of long waiters. Underneath, the list is a stock that can’t be summed over time, the percentiles need individual waits, and the treatments that shrink the list are dated differently from the referrals that grow it. This project is the semantic model I’d build for it, written as TMDL text files so every table, relationship and measure can be read and reviewed in Git.

The data is synthetic: about 58,000 referral-to-treatment (RTT) pathways for an invented acute trust. A Python script generates it, writes the CSVs the model loads, and computes every KPI in pandas with the same definitions as the DAX, so the charts below show what the report would show.

uv run python projects/rtt-waiting-list-model/run.py

The reporting problem

An RTT clock starts when a referral for consultant-led care is received and stops at first definitive treatment, or when a decision is made that treatment isn’t needed. While the clock runs, the pathway is on the waiting list: an incomplete pathway. The NHS Constitution standard in England is that 92% of patients on incomplete pathways should have waited no more than 18 weeks from referral. Long waits past 52, 65 and 78 weeks are tracked separately, because that’s where the clinical risk and the scrutiny sit.

So the report has to answer four questions every week. How big is the list? What share is within 18 weeks? How many long waiters are there, and in which specialties? Is it getting better? Two definitions drive every number here:

• Weeks waited are completed weeks from clock start to the snapshot date: days ÷ 7, rounded down.
• Within 18 weeks means fewer than 18 completed weeks, so under 126 days.

The data

The generator runs a weekly simulation from January 2021. Each open pathway has a chance of a clock stop each week that depends on specialty, referral priority, whether it ends in an admission, and a per-pathway random “frailty” that gives waits their long right tail. The extract keeps the 5,505 pathways on the list on Sunday 2 April 2023 and the 52,545 clock starts from then to Sunday 28 September 2025: 58,050 pathways across ten specialties.

The story is planted, and the README in the code download lists every pattern:

• Capacity falls to two thirds of normal between May 2023 and January 2024, so the list grows.
• From mid-2024 six specialties recover to above-normal capacity and add a push on routine pathways past 40 weeks. Trauma & Orthopaedics, ENT, Gynaecology and Oral Surgery settle below normal and keep their long waiters.
• Christmas and August dip, referrals grow 3% a year, and two validation sprints remove pathways in bulk.

The model design

All nine relationships are many-to-one and filter in one direction, from dimension to fact. Pathways meets Date twice: an active relationship on clock start date and an inactive one on clock stop date, switched on inside the clock-stop measures.

Why a snapshot fact

The waiting list on a date is the set of pathways with a clock start on or before it and no clock stop yet. DAX can compute that from the pathway fact, but it’s a range filter for every point on every chart, and a weekly trend by specialty is 1,310 of them. The snapshot fact does that work once, upstream of the model, at exactly the grain the report needs. It adds up across specialty, priority and wait band, and must never add up across time. A plain SUM over the four Sundays of September 2025 gives 21,838; the list on 28 September was 5,494.

Weekly is a natural grain: NHS England’s Waiting List Minimum Data Set is a weekly collection that sits alongside the monthly RTT statistics.

Why keep the pathway fact

A median can’t be read from band counts. The median and 92nd percentile waits need each pathway’s wait, so those two measures read the pathway fact. So do clock starts and clock stops, which are events, and any drill-through to individual pathways.

Wait bands on the KPI boundaries

The ten bands (0 to <6 weeks up to 104+ weeks) are cut so that 18, 52, 65 and 78 weeks are all band edges. Every KPI that counts pathways above or below a threshold is then an exact sum of whole bands, and the snapshot stays compact: 19,884 rows instead of one row per week waited.

TMDL excerpts

The model is a PBIP semantic model folder. Each table is one file, which is what makes a pull request readable.

RTT.SemanticModel/
├── definition.pbism
└── definition/
    ├── database.tmdl
    ├── model.tmdl
    ├── expressions.tmdl
    ├── relationships.tmdl
    └── tables/
        ├── Clock Stop Type.tmdl
        ├── Date.tmdl
        ├── Pathways.tmdl
        ├── Priority.tmdl
        ├── Specialty.tmdl
        ├── Wait Band.tmdl
        └── Waiting List Snapshot.tmdl

A column with a description, a hidden sort key and a sort order, from Wait Band.tmdl. The files use tab indentation, as Power BI Desktop writes it:

table 'Wait Band'

	column 'Wait Band Key'
		dataType: int64
		isHidden
		isKey
		formatString: 0
		summarizeBy: none
		sourceColumn: wait_band_key

	column 'Wait Band'
		dataType: string
		summarizeBy: none
		sourceColumn: wait_band
		sortByColumn: 'Wait Band Key'

	/// Lower edge of the band in completed weeks. The KPI measures filter on this column.
	column 'Min Weeks'
		dataType: int64
		isHidden
		formatString: 0
		summarizeBy: none
		sourceColumn: min_weeks

The CSV folder is a Power Query parameter in expressions.tmdl, and every partition builds its file path from it:

/// Folder holding the seven CSV files. Keep the trailing backslash.
expression CsvFolder = "C:\RTT\data\" meta [IsParameterQuery = true, Type = "Text", IsParameterQueryRequired = true]

	annotation PBI_ResultType = Text

	partition Pathways = m
		mode: import
		source =
				let
					Source = Csv.Document(File.Contents(CsvFolder & "fact_pathway.csv"), [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]),
					Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
					Blanks = Table.ReplaceValue(Promoted, "", null, Replacer.ReplaceValue, {"clock_stop_date", "stop_type_key"}),
					Typed = Table.TransformColumnTypes(
						Blanks,
						{
							{"pathway_id", Int64.Type},
							{"specialty_key", Int64.Type},
							{"priority_key", Int64.Type},
							{"clock_start_date", type date},
							{"clock_stop_date", type date},
							{"stop_type_key", Int64.Type}
						}
					)
				in
					Typed

And the inactive relationship, from relationships.tmdl. The GUID name follows the Power BI Desktop convention; the two column lines are what a reviewer reads:

relationship 4266e0c7-e6fe-5f23-805c-6d368d813243
	isActive: false
	fromColumn: Pathways.'Clock Stop Date'
	toColumn: Date.Date

The whole folder ships in the project code download below.

Key DAX measures

All 18 measures live in the two fact tables’ TMDL files, each with a /// description. run.py reads them back out of the TMDL and writes rtt-measures.dax, a DAX query you can paste into DAX query view.

The list size takes the last Sunday inside the current date filter. The date lookup removes the specialty, priority and wait band filters, so every row of a visual uses the same week:

Latest Snapshot Date =
CALCULATE (
    MAX ( 'Waiting List Snapshot'[Snapshot Date] ),
    REMOVEFILTERS ( Specialty ),
    REMOVEFILTERS ( Priority ),
    REMOVEFILTERS ( 'Wait Band' )
)

Waiting List =
VAR AsAt = [Latest Snapshot Date]
RETURN
    CALCULATE ( SUM ( 'Waiting List Snapshot'[Pathway Count] ), 'Date'[Date] = AsAt )

Without those REMOVEFILTERS, a specialty with nobody in a band this week falls back to the last Sunday it had someone there. For the 104+ weeks band that turns a correct total of 3 into rows that add up to 9.

The threshold measures filter on the band’s lower edge:

% Within 18 Weeks = DIVIDE ( [Within 18 Weeks], [Waiting List] )
Within 18 Weeks = CALCULATE ( [Waiting List], 'Wait Band'[Min Weeks] < 18 )
52+ Week Waiters = CALCULATE ( [Waiting List], 'Wait Band'[Min Weeks] >= 52 )

The median rebuilds the list on the snapshot date from the pathway fact, then takes completed weeks per pathway. The 92nd percentile is the same with PERCENTILEX.INC:

Median Wait (Weeks) =
VAR AsAt = [Latest Snapshot Date]
VAR OpenPathways =
    CALCULATETABLE (
        Pathways,
        REMOVEFILTERS ( 'Date' ),
        Pathways[Clock Start Date] <= AsAt,
        ISBLANK ( Pathways[Clock Stop Date] ) || Pathways[Clock Stop Date] > AsAt
    )
RETURN
    MEDIANX (
        OpenPathways,
        QUOTIENT ( DATEDIFF ( Pathways[Clock Start Date], AsAt, DAY ), 7 )
    )

Comparisons anchor on the snapshot date rather than on the date filter. 364 days is 52 weeks, so a Sunday lands on a Sunday:

Waiting List Same Week LY =
VAR AsAt = [Latest Snapshot Date]
RETURN
    CALCULATE ( [Waiting List], REMOVEFILTERS ( 'Date' ), 'Date'[Date] = AsAt - 364 )

Clock stops are events, so they suit the built-in functions. They switch on the inactive relationship:

Clock Stops Admitted =
CALCULATE (
    COUNTROWS ( Pathways ),
    USERELATIONSHIP ( Pathways[Clock Stop Date], 'Date'[Date] ),
    'Clock Stop Type'[Clock Stop Type] = "Admitted"
)

Treatments Same Period LY = CALCULATE ( [Treatments], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

DAX for waiting-list KPIs walks through each pattern and the mistakes it avoids.

Results

The list grew from 5,505 on 2 April 2023 to a peak of 7,115 on 17 March 2024, then came back to 5,494 by 28 September 2025. The last week added 34 pathways; the same week a year earlier had 5,339.

Data table

Snapshot date	Waiting list	Same week last year
2023-04-02	5,505
2023-04-09	5,538
2023-04-16	5,505
2023-04-23	5,462
2023-04-30	5,453
2023-05-07	5,439
2023-05-14	5,463
2023-05-21	5,474
2023-05-28	5,504
2023-06-04	5,518
2023-06-11	5,508
2023-06-18	5,555
2023-06-25	5,591
2023-07-02	5,594
2023-07-09	5,654
2023-07-16	5,692
2023-07-23	5,708
2023-07-30	5,734
2023-08-06	5,767
2023-08-13	5,745
2023-08-20	5,727
2023-08-27	5,722
2023-09-03	5,730
2023-09-10	5,765
2023-09-17	5,784
2023-09-24	5,856
2023-10-01	5,863
2023-10-08	5,879
2023-10-15	5,950
2023-10-22	5,998
2023-10-29	6,068
2023-11-05	6,058
2023-11-12	6,108
2023-11-19	6,186
2023-11-26	6,244
2023-12-03	6,287
2023-12-10	6,348
2023-12-17	6,443
2023-12-24	6,548
2023-12-31	6,611
2024-01-07	6,655
2024-01-14	6,715
2024-01-21	6,799
2024-01-28	6,922
2024-02-04	6,998
2024-02-11	6,982
2024-02-18	7,006
2024-02-25	7,078
2024-03-03	7,051
2024-03-10	7,061
2024-03-17	7,115
2024-03-24	7,029
2024-03-31	6,955	5,505
2024-04-07	6,952	5,538
2024-04-14	6,928	5,505
2024-04-21	6,887	5,462
2024-04-28	6,851	5,453
2024-05-05	6,788	5,439
2024-05-12	6,754	5,463
2024-05-19	6,665	5,474
2024-05-26	6,618	5,504
2024-06-02	6,567	5,518
2024-06-09	6,514	5,508
2024-06-16	6,417	5,555
2024-06-23	6,317	5,591
2024-06-30	6,282	5,594
2024-07-07	6,193	5,654
2024-07-14	6,121	5,692
2024-07-21	6,076	5,708
2024-07-28	6,052	5,734
2024-08-04	5,975	5,767
2024-08-11	5,819	5,745
2024-08-18	5,739	5,727
2024-08-25	5,676	5,722
2024-09-01	5,612	5,730
2024-09-08	5,528	5,765
2024-09-15	5,448	5,784
2024-09-22	5,428	5,856
2024-09-29	5,339	5,863
2024-10-06	5,338	5,879
2024-10-13	5,369	5,950
2024-10-20	5,369	5,998
2024-10-27	5,388	6,068
2024-11-03	5,379	6,058
2024-11-10	5,364	6,108
2024-11-17	5,366	6,186
2024-11-24	5,378	6,244
2024-12-01	5,393	6,287
2024-12-08	5,395	6,348
2024-12-15	5,458	6,443
2024-12-22	5,478	6,548
2024-12-29	5,510	6,611
2025-01-05	5,528	6,655
2025-01-12	5,549	6,715
2025-01-19	5,588	6,799
2025-01-26	5,623	6,922
2025-02-02	5,608	6,998
2025-02-09	5,574	6,982
2025-02-16	5,588	7,006
2025-02-23	5,539	7,078
2025-03-02	5,507	7,051
2025-03-09	5,491	7,061
2025-03-16	5,487	7,115
2025-03-23	5,485	7,029
2025-03-30	5,475	6,955
2025-04-06	5,441	6,952
2025-04-13	5,444	6,928
2025-04-20	5,434	6,887
2025-04-27	5,443	6,851
2025-05-04	5,407	6,788
2025-05-11	5,436	6,754
2025-05-18	5,442	6,665
2025-05-25	5,467	6,618
2025-06-01	5,517	6,567
2025-06-08	5,520	6,514
2025-06-15	5,497	6,417
2025-06-22	5,506	6,317
2025-06-29	5,470	6,282
2025-07-06	5,450	6,193
2025-07-13	5,462	6,121
2025-07-20	5,480	6,076
2025-07-27	5,512	6,052
2025-08-03	5,475	5,975
2025-08-10	5,478	5,819
2025-08-17	5,462	5,739
2025-08-24	5,432	5,676
2025-08-31	5,408	5,612
2025-09-07	5,433	5,528
2025-09-14	5,451	5,448
2025-09-21	5,460	5,428
2025-09-28	5,494	5,339

Synthetic data. Source: projects/rtt-waiting-list-model.

The share within 18 weeks fell from 64.2% to a low of 57.9% on 21 April 2024 and recovered to 65.5%. At trust level that’s still 26.5 points short of the standard. The specialty lines show why a trust average hides the problem: Cardiology is at 89.0%, Trauma & Orthopaedics at 53.6%, slightly worse than where it started.

Data table

Snapshot date	Trust	Trauma & Orthopaedics	Cardiology
2023-04-02	64%	54%	77%
2023-04-09	64%	54%	78%
2023-04-16	64%	54%	77%
2023-04-23	63%	54%	78%
2023-04-30	64%	54%	79%
2023-05-07	65%	55%	79%
2023-05-14	65%	54%	78%
2023-05-21	65%	54%	78%
2023-05-28	65%	54%	77%
2023-06-04	65%	55%	76%
2023-06-11	65%	55%	77%
2023-06-18	65%	55%	78%
2023-06-25	65%	54%	78%
2023-07-02	65%	54%	80%
2023-07-09	65%	54%	80%
2023-07-16	65%	54%	81%
2023-07-23	65%	53%	80%
2023-07-30	65%	54%	81%
2023-08-06	65%	53%	82%
2023-08-13	65%	53%	82%
2023-08-20	64%	53%	82%
2023-08-27	64%	53%	82%
2023-09-03	64%	54%	84%
2023-09-10	64%	53%	82%
2023-09-17	64%	54%	82%
2023-09-24	64%	54%	82%
2023-10-01	64%	54%	83%
2023-10-08	64%	52%	83%
2023-10-15	64%	54%	84%
2023-10-22	64%	53%	85%
2023-10-29	64%	53%	85%
2023-11-05	63%	53%	82%
2023-11-12	63%	52%	80%
2023-11-19	63%	52%	79%
2023-11-26	63%	53%	81%
2023-12-03	63%	54%	81%
2023-12-10	63%	53%	80%
2023-12-17	63%	53%	79%
2023-12-24	63%	53%	78%
2023-12-31	62%	53%	76%
2024-01-07	62%	53%	76%
2024-01-14	62%	53%	74%
2024-01-21	62%	53%	75%
2024-01-28	61%	54%	74%
2024-02-04	61%	53%	73%
2024-02-11	61%	54%	75%
2024-02-18	61%	53%	74%
2024-02-25	61%	52%	75%
2024-03-03	60%	52%	74%
2024-03-10	60%	52%	74%
2024-03-17	60%	52%	75%
2024-03-24	59%	52%	73%
2024-03-31	58%	51%	73%
2024-04-07	58%	50%	72%
2024-04-14	58%	50%	73%
2024-04-21	58%	49%	74%
2024-04-28	58%	49%	77%
2024-05-05	58%	50%	78%
2024-05-12	59%	50%	78%
2024-05-19	59%	52%	79%
2024-05-26	59%	52%	80%
2024-06-02	59%	52%	81%
2024-06-09	59%	51%	80%
2024-06-16	59%	52%	79%
2024-06-23	59%	52%	79%
2024-06-30	58%	52%	79%
2024-07-07	59%	53%	80%
2024-07-14	59%	52%	81%
2024-07-21	60%	53%	82%
2024-07-28	60%	54%	84%
2024-08-04	61%	55%	83%
2024-08-11	61%	55%	83%
2024-08-18	62%	56%	85%
2024-08-25	62%	56%	86%
2024-09-01	63%	57%	85%
2024-09-08	64%	57%	87%
2024-09-15	65%	57%	89%
2024-09-22	65%	58%	88%
2024-09-29	66%	59%	88%
2024-10-06	66%	58%	88%
2024-10-13	67%	59%	86%
2024-10-20	67%	58%	87%
2024-10-27	67%	58%	89%
2024-11-03	67%	57%	89%
2024-11-10	67%	57%	90%
2024-11-17	67%	57%	90%
2024-11-24	67%	56%	89%
2024-12-01	67%	56%	88%
2024-12-08	67%	56%	89%
2024-12-15	67%	56%	88%
2024-12-22	67%	57%	85%
2024-12-29	66%	56%	84%
2025-01-05	66%	55%	84%
2025-01-12	66%	55%	84%
2025-01-19	66%	56%	85%
2025-01-26	66%	55%	84%
2025-02-02	65%	55%	83%
2025-02-09	66%	54%	86%
2025-02-16	65%	54%	86%
2025-02-23	66%	54%	87%
2025-03-02	65%	54%	87%
2025-03-09	65%	55%	87%
2025-03-16	65%	56%	86%
2025-03-23	65%	56%	85%
2025-03-30	65%	56%	85%
2025-04-06	65%	56%	86%
2025-04-13	65%	56%	86%
2025-04-20	64%	55%	87%
2025-04-27	64%	54%	87%
2025-05-04	65%	55%	87%
2025-05-11	66%	56%	88%
2025-05-18	66%	57%	88%
2025-05-25	66%	57%	88%
2025-06-01	66%	56%	89%
2025-06-08	67%	57%	90%
2025-06-15	67%	58%	90%
2025-06-22	68%	58%	92%
2025-06-29	68%	59%	91%
2025-07-06	68%	58%	91%
2025-07-13	68%	58%	91%
2025-07-20	68%	57%	91%
2025-07-27	68%	57%	90%
2025-08-03	67%	56%	88%
2025-08-10	67%	56%	87%
2025-08-17	66%	55%	88%
2025-08-24	66%	54%	89%
2025-08-31	66%	55%	90%
2025-09-07	66%	54%	91%
2025-09-14	66%	54%	89%
2025-09-21	66%	53%	90%
2025-09-28	66%	54%	89%

Synthetic data. Source: projects/rtt-waiting-list-model.

Long waits are now concentrated. Of the 281 pathways past 52 weeks, 263 are in four specialties: Trauma & Orthopaedics (108), Gynaecology (75), ENT (54) and Oral Surgery (26).

Data table

Specialty	52+ week waiters
Trauma & Orthopaedics	108
Gynaecology	75
ENT	54
Oral Surgery	26
General Surgery	5
Ophthalmology	4
Gastroenterology	4
Urology	3
Cardiology	1
Dermatology	1

Synthetic data. Source: projects/rtt-waiting-list-model.

The other six specialties went from 106 long waiters at the start to a peak of 148 and are down to 18. The four with long tails never cleared theirs.

Data table

Snapshot date	Trauma & Orthopaedics	Gynaecology	ENT	Oral Surgery	Other six
2023-04-02	106	66	39	30	106
2023-04-09	107	67	40	27	100
2023-04-16	104	65	40	26	102
2023-04-23	107	65	38	24	98
2023-04-30	110	61	37	24	98
2023-05-07	106	59	34	22	90
2023-05-14	109	63	37	21	94
2023-05-21	108	64	39	18	92
2023-05-28	110	68	41	18	88
2023-06-04	118	68	43	20	84
2023-06-11	116	66	43	19	89
2023-06-18	116	70	41	18	89
2023-06-25	115	66	39	20	83
2023-07-02	116	65	38	21	87
2023-07-09	119	65	41	22	87
2023-07-16	125	65	47	21	87
2023-07-23	126	65	41	22	88
2023-07-30	127	65	42	22	90
2023-08-06	127	64	42	24	92
2023-08-13	123	59	44	23	89
2023-08-20	124	58	43	27	89
2023-08-27	123	59	42	26	88
2023-09-03	116	58	44	26	98
2023-09-10	111	55	43	28	99
2023-09-17	115	53	41	29	96
2023-09-24	120	56	39	30	97
2023-10-01	120	60	42	29	100
2023-10-08	129	59	45	31	101
2023-10-15	132	60	47	33	99
2023-10-22	126	62	47	32	98
2023-10-29	128	64	47	33	92
2023-11-05	132	60	45	40	94
2023-11-12	132	57	46	40	95
2023-11-19	131	57	49	40	98
2023-11-26	129	57	52	39	95
2023-12-03	124	64	51	40	100
2023-12-10	124	66	54	38	109
2023-12-17	131	64	53	37	114
2023-12-24	133	65	53	38	114
2023-12-31	132	67	53	38	116
2024-01-07	131	66	54	35	120
2024-01-14	135	65	55	38	124
2024-01-21	133	65	56	37	126
2024-01-28	133	70	57	37	129
2024-02-04	133	70	55	38	133
2024-02-11	129	70	56	40	138
2024-02-18	135	74	59	40	134
2024-02-25	136	75	65	45	136
2024-03-03	135	75	71	47	136
2024-03-10	135	75	73	46	135
2024-03-17	139	74	75	46	135
2024-03-24	133	72	78	40	136
2024-03-31	139	75	78	39	134
2024-04-07	142	76	82	39	138
2024-04-14	140	79	80	42	141
2024-04-21	142	79	78	42	137
2024-04-28	139	83	78	44	136
2024-05-05	142	84	83	45	138
2024-05-12	146	80	87	44	139
2024-05-19	138	84	83	48	142
2024-05-26	133	86	84	49	139
2024-06-02	138	92	84	48	145
2024-06-09	136	97	82	44	141
2024-06-16	139	95	90	44	143
2024-06-23	132	93	90	45	146
2024-06-30	137	94	82	43	148
2024-07-07	128	90	79	44	136
2024-07-14	130	85	77	44	126
2024-07-21	125	84	78	39	113
2024-07-28	126	88	81	41	103
2024-08-04	129	90	83	39	97
2024-08-11	125	92	82	38	95
2024-08-18	125	86	81	36	84
2024-08-25	129	79	79	38	75
2024-09-01	127	83	79	42	70
2024-09-08	123	78	76	41	65
2024-09-15	121	75	69	38	56
2024-09-22	114	78	69	35	57
2024-09-29	112	73	72	36	55
2024-10-06	109	73	68	32	51
2024-10-13	111	74	70	30	48
2024-10-20	118	74	68	29	39
2024-10-27	113	72	63	28	36
2024-11-03	113	74	62	27	33
2024-11-10	114	73	63	28	35
2024-11-17	105	73	61	30	39
2024-11-24	108	73	57	29	39
2024-12-01	109	70	54	28	40
2024-12-08	106	73	58	26	37
2024-12-15	108	74	63	27	35
2024-12-22	107	73	64	28	32
2024-12-29	106	73	63	29	27
2025-01-05	103	77	57	29	27
2025-01-12	101	76	61	28	25
2025-01-19	105	78	61	28	26
2025-01-26	105	83	62	31	24
2025-02-02	111	80	63	31	22
2025-02-09	109	79	64	32	20
2025-02-16	110	80	63	32	19
2025-02-23	113	83	61	31	15
2025-03-02	107	79	55	29	13
2025-03-09	107	81	57	28	13
2025-03-16	110	84	59	26	14
2025-03-23	103	87	57	23	13
2025-03-30	109	85	62	27	15
2025-04-06	113	82	61	32	13
2025-04-13	113	85	60	32	12
2025-04-20	110	87	62	34	12
2025-04-27	115	95	59	32	12
2025-05-04	116	91	60	32	11
2025-05-11	112	91	58	29	12
2025-05-18	112	88	60	30	14
2025-05-25	118	86	59	26	15
2025-06-01	122	88	57	27	14
2025-06-08	115	89	58	30	14
2025-06-15	112	87	55	29	17
2025-06-22	111	89	58	33	17
2025-06-29	105	88	55	30	18
2025-07-06	107	84	57	29	15
2025-07-13	111	80	60	30	15
2025-07-20	104	79	58	30	13
2025-07-27	107	73	56	31	20
2025-08-03	116	76	54	31	16
2025-08-10	117	72	52	31	16
2025-08-17	119	77	55	28	15
2025-08-24	122	75	57	30	15
2025-08-31	116	74	55	24	17
2025-09-07	116	72	52	27	16
2025-09-14	111	76	51	26	17
2025-09-21	115	75	55	25	17
2025-09-28	108	75	54	26	18

Synthetic data. Source: projects/rtt-waiting-list-model.

Against the same Sunday a year earlier, the tail past 52 weeks is thinner in every band from 52 to 104 weeks, while the 18 to 26 week band has grown from 538 to 678. That is the next cohort of long waiters if capacity doesn’t hold.

Data table

Weeks waited	28 Sep 2025	29 Sep 2024
0 to <6 weeks	1,849	1,838
6 to <12 weeks	1,068	1,070
12 to <18 weeks	681	629
18 to <26 weeks	678	538
26 to <39 weeks	603	575
39 to <52 weeks	334	341
52 to <65 weeks	167	201
65 to <78 weeks	80	100
78 to <104 weeks	31	44
104+ weeks	3	3

Synthetic data. Source: projects/rtt-waiting-list-model.

Between April 2023 and September 2025 there were 40,162 non-admitted and 11,299 admitted clock stops, plus 1,095 validation removals. The two validation sprints stand out in the removals line. Removals shorten the list without treating anyone, which is why they have their own measure and stay out of Treatments.

Data table

Week ending	Admitted	Non-admitted	Validation removal
2023-04-09	78	296	5
2023-04-16	91	337	5
2023-04-23	87	307	5
2023-04-30	95	293	8
2023-05-07	94	311	7
2023-05-14	97	278	2
2023-05-21	82	287	3
2023-05-28	79	316	6
2023-06-04	87	300	8
2023-06-11	95	301	3
2023-06-18	72	284	6
2023-06-25	82	293	1
2023-07-02	88	302	2
2023-07-09	81	259	4
2023-07-16	79	292	5
2023-07-23	93	281	4
2023-07-30	80	308	6
2023-08-06	78	305	6
2023-08-13	84	287	6
2023-08-20	71	283	1
2023-08-27	72	293	2
2023-09-03	86	275	0
2023-09-10	86	277	4
2023-09-17	82	307	7
2023-09-24	75	269	2
2023-10-01	94	292	3
2023-10-08	68	274	1
2023-10-15	61	282	9
2023-10-22	85	268	3
2023-10-29	65	302	4
2023-11-05	70	316	4
2023-11-12	77	260	5
2023-11-19	72	263	2
2023-11-26	66	271	5
2023-12-03	69	303	3
2023-12-10	78	256	3
2023-12-17	50	255	1
2023-12-24	59	232	4
2023-12-31	32	119	9
2024-01-07	43	126	4
2024-01-14	53	289	5
2024-01-21	64	266	3
2024-01-28	82	238	6
2024-02-04	67	252	3
2024-02-11	86	315	4
2024-02-18	78	292	4
2024-02-25	76	299	6
2024-03-03	83	307	5
2024-03-10	84	325	4
2024-03-17	90	292	6
2024-03-24	104	345	4
2024-03-31	87	370	8
2024-04-07	84	333	5
2024-04-14	91	333	3
2024-04-21	102	334	7
2024-04-28	86	356	4
2024-05-05	117	329	8
2024-05-12	94	352	7
2024-05-19	109	374	5
2024-05-26	106	348	5
2024-06-02	107	361	7
2024-06-09	113	351	5
2024-06-16	119	371	4
2024-06-23	114	391	5
2024-06-30	100	333	5
2024-07-07	123	351	5
2024-07-14	128	365	6
2024-07-21	107	363	8
2024-07-28	114	361	3
2024-08-04	112	358	8
2024-08-11	97	333	65
2024-08-18	100	281	64
2024-08-25	111	307	57
2024-09-01	86	312	41
2024-09-08	87	340	56
2024-09-15	125	317	48
2024-09-22	99	320	49
2024-09-29	87	347	60
2024-10-06	91	308	2
2024-10-13	92	312	4
2024-10-20	73	314	3
2024-10-27	93	314	3
2024-11-03	89	327	7
2024-11-10	85	347	6
2024-11-17	95	324	5
2024-11-24	62	323	3
2024-12-01	110	322	4
2024-12-08	94	288	5
2024-12-15	75	291	5
2024-12-22	93	320	6
2024-12-29	48	144	6
2025-01-05	49	157	1
2025-01-12	76	327	4
2025-01-19	76	317	3
2025-01-26	97	295	7
2025-02-02	87	337	5
2025-02-09	81	369	7
2025-02-16	92	334	2
2025-02-23	90	351	3
2025-03-02	108	349	8
2025-03-09	96	328	4
2025-03-16	72	353	2
2025-03-23	103	329	2
2025-03-30	90	349	5
2025-04-06	84	342	4
2025-04-13	84	342	0
2025-04-20	82	329	2
2025-04-27	85	341	3
2025-05-04	87	346	2
2025-05-11	74	326	5
2025-05-18	83	331	5
2025-05-25	81	313	9
2025-06-01	85	305	4
2025-06-08	88	326	29
2025-06-15	102	321	38
2025-06-22	98	294	29
2025-06-29	99	343	39
2025-07-06	94	310	1
2025-07-13	104	322	4
2025-07-20	99	301	5
2025-07-27	87	317	5
2025-08-03	90	368	4
2025-08-10	88	285	3
2025-08-17	93	302	2
2025-08-24	92	315	6
2025-08-31	94	315	3
2025-09-07	88	298	3
2025-09-14	90	308	7
2025-09-21	103	322	3
2025-09-28	73	340	2

Synthetic data. Source: projects/rtt-waiting-list-model.

At the latest snapshot the median wait is 11.0 weeks and the 92nd percentile is 44.6 weeks, down from 13.0 and 49.0 at the April 2024 low point.

What was tested, and what wasn’t

• TMDL structure: tested. A small .NET program in the project loads the folder with TmdlSerializer from Microsoft’s Analysis Services client library (the Tabular Object Model). That fails on syntax errors, unknown properties and broken references, and I checked that it does by breaking a sort-by column on purpose. Serialising the loaded model back out reproduces the files byte for byte.
• DAX syntax: tested. All 18 measures and the full query file parse cleanly in DAX Formatter. That checks syntax, not function names or results.
• KPI logic: tested in pandas. Each pandas function follows one measure’s definition, and run.py checks that the two fact CSVs agree on list size and within-18-weeks counts at all 131 snapshots.
• Not tested: opening the model in Power BI Desktop, running the Power Query steps, refreshing, and evaluating the DAX. I didn’t have Power BI Desktop or an Analysis Services engine to run it on. The README explains how to open it, and the DAX query’s output by specialty should match the numbers above.

Limits

• The waits come from a hazard model, not from any real trust. The shape is plausible; the levels are not calibrated to anything.
• “Within 18 weeks” here means fewer than 18 completed weeks. Check that against your local RTT reporting rules before reusing the measures: a one-day difference at the boundary changes the count.
• Pathways that stopped before 2 April 2023 are not in the extract, so Clock Starts before that date only counts people who were still waiting.
• No clock pauses, no patient-level identifiers, and one pathway per referral. Real RTT data has to handle all three.
• The median and 92nd percentile ignore a wait band filter, because the pathway fact has no relationship to Wait Band.
• The median measure scans the pathway fact at query time. At 58,050 rows that’s fine. At millions I’d precompute each pathway’s wait at each snapshot, or accept banded percentiles.

What I’d do next

• Open it in Power BI Desktop, refresh, and compare the DAX query’s output with run.py, then build the report pages on top.
• Add a DAX query test to CI: deploy to a workspace, run the query over XMLA, and diff the result against the pandas numbers.
• Split incomplete pathways into those with and without a decision to admit, which is the view theatre capacity planning needs.
• Replace the time-comparison measures with a calculation group once there are more than two of them. The theatre utilisation semantic model does this, and calculation groups in practice covers the pitfalls.