DAX for waiting-list KPIs

Waiting-list measures go wrong in DAX in a small number of predictable ways, and almost all of them come from one fact: the waiting list is a stock, not a flow. You can count referrals and treatments over any period, but the list only exists at a point in time. Get that right and the rest of the measures follow.

The examples are the measures from my RTT waiting list semantic model, on synthetic data for an invented acute trust. Every number below comes from that project’s code, which computes each KPI in pandas with the same definition as the DAX.

Two facts, two kinds of measure

The model has two fact tables because the questions come in two kinds.

The snapshot is a periodic snapshot: the same pathway appears every week until its clock stops. It adds up across specialty, priority and wait band, but not across time. The pathway table has one row per pathway with its clock start and clock stop dates, an accumulating snapshot in Kimball’s terms. Each clock start and each clock stop happens once, so counts of them add up across everything, including time, like any event fact. That difference decides which DAX pattern each measure needs.

Never SUM across snapshots

A plain SUM over the snapshot is correct only when one Sunday is in the filter context. Put a month on the rows and it adds up every Sunday in the month:

SUM reports 21,838 for a month in which no Sunday count was above 5,494. It also jumps in months with five Sundays. Waiting-list measures are semi-additive: sum over the other dimensions, take one point in time for the date.

Three ways to pick the point in time

The first attempt is usually LASTDATE on the date table:

Waiting List (LASTDATE) =
CALCULATE (
    SUM ( 'Waiting List Snapshot'[Pathway Count] ),
    LASTDATE ( 'Date'[Date] )
)

LASTDATE returns the last date in the current context from the column you give it. For September 2025 that’s Tuesday 30 September, and there’s no snapshot on a Tuesday, so the measure returns blank. With no date filter at all it returns 31 December 2025, also blank. It works only when the snapshot happens to land on the last day of every period, which weekly Sunday snapshots never do.

LASTNONBLANK asks for the last date that has data:

Waiting List (LASTNONBLANK) =
CALCULATE (
    SUM ( 'Waiting List Snapshot'[Pathway Count] ),
    LASTNONBLANK (
        'Date'[Date],
        CALCULATE ( SUM ( 'Waiting List Snapshot'[Pathway Count] ) )
    )
)

That gives 5,494 for September. It evaluates the inner expression for every date in context, and dax.guide warns that LASTNONBLANK can cause performance and memory issues. It also has a quieter problem, which the third version fixes.

The model finds the last snapshot date with MAX, in its own measure:

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 )

MAX returns a scalar, which is what you want for comparing and for date arithmetic. dax.guide makes the same point: use MAX instead of LASTDATE when the result must be a scalar.

Why the REMOVEFILTERS matter

Without them, “latest snapshot” means “latest snapshot that has rows under all of the current filters”. In a matrix of specialty by wait band, a specialty with nobody in a band this week falls back to the last Sunday it had someone there, and shows that old count as if it were current.

In the RTT data, only ENT (2) and Trauma & Orthopaedics (1) have anyone past 104 weeks on 28 September 2025, so the correct total for that band is 3. Without the REMOVEFILTERS, six more specialties show 1 each, from dates as far back as February 2024. The rows add up to 9 under a total of 3. LASTNONBLANK has the same problem, because its inner SUM runs under the same filters.

Removing the dimension filters inside the date lookup makes every cell use the same Sunday. The date filter stays, so a month still shows its last Sunday and a financial year its last week.

Percentage within 18 weeks

The NHS Constitution standard is that 92% of patients on incomplete pathways should have waited no more than 18 weeks from referral. In this model “within 18 weeks” means fewer than 18 completed weeks, and the wait bands are cut so that 18 is a band edge:

Within 18 Weeks =
CALCULATE ( [Waiting List], 'Wait Band'[Min Weeks] < 18 )

% Within 18 Weeks =
DIVIDE ( [Within 18 Weeks], [Waiting List] )

52+ Week Waiters =
CALCULATE ( [Waiting List], 'Wait Band'[Min Weeks] >= 52 )

Because these call [Waiting List], they inherit its choice of Sunday. DIVIDE returns BLANK when the denominator is zero or BLANK. The / operator wouldn’t raise an error there either: it can return infinity or NaN, which then shows up in a visual.

Filter on the band’s lower edge. The tempting alternative, 'Wait Band'[Max Weeks] <= 18, is wrong in a way no test on typical rows would show: the open-ended 104+ weeks band has a blank upper edge, and every DAX comparison operator except == treats BLANK as zero. The longest waiters would count as within 18 weeks.

At the latest snapshot the trust is at 65.5%, with 281 pathways past 52 weeks, 114 past 65 and 34 past 78.

Median and 92nd percentile waits

A median can’t be read exactly from band counts, so these measures rebuild the list at the snapshot date from the pathway fact and take each pathway’s completed weeks:

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 )
    )

Three details:

• REMOVEFILTERS ( ‘Date’ ). The active relationship runs from Date to clock start. Without this, a September filter would keep only pathways referred in September.
• ISBLANK on the stop date. An open pathway has a blank stop date, and a comparison treats BLANK as 30 December 1899, so Pathways[Clock Stop Date] > AsAt alone would drop every pathway still waiting.
• QUOTIENT ( DATEDIFF ( …, DAY ), 7 ). Completed weeks, rounded down, the same definition as the bands.

The 92nd percentile is the same measure with PERCENTILEX.INC ( OpenPathways, <weeks>, 0.92 ). When the 92nd percentile falls between two pathways’ waits, PERCENTILEX.INC interpolates, so it can return fractions of a week. At the latest snapshot the median is 11.0 weeks and the 92nd percentile 44.6. In pandas the matching calls are np.median, which also averages the two middle values, and np.percentile with its default linear method.

The cost is a scan of the pathway table per cell. At 58,050 rows that’s nothing. With millions of pathways I’d precompute each pathway’s wait at each snapshot, or report banded percentiles and say so.

Week-on-week and year-on-year

Comparisons on a snapshot measure should anchor on the snapshot date, not on the date filter:

Waiting List WoW Change =
VAR AsAt = [Latest Snapshot Date]
VAR PriorWeek =
    CALCULATE ( [Waiting List], REMOVEFILTERS ( 'Date' ), 'Date'[Date] = AsAt - 7 )
RETURN
    IF ( NOT ISBLANK ( PriorWeek ), [Waiting List] - PriorWeek )

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

On 28 September 2025 that gives +34 on the week (5,460 to 5,494) and 5,339 for Sunday 29 September 2024. 364 days is exactly 52 weeks, so a Sunday lands on a Sunday. REMOVEFILTERS ( ‘Date’ ) lets the prior week sit in the previous month.

The obvious alternative, CALCULATE ( [Waiting List], DATEADD ( 'Date'[Date], -364, DAY ) ), shifts whatever dates are in the filter. For a single week or a whole month that works. On a card with no date filter, or a financial year still in progress, it shifts dates that run past the last snapshot. The date table ends on 31 December 2025, so the shifted range ends on 1 January 2025 and the measure returns the list on Sunday 29 December 2024 instead of the same week last year.

Event measures are different. Treatments happen once, on a date, so the built-in time intelligence functions fit them:

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

Treatments = [Clock Stops Admitted] + [Clock Stops Non-admitted]

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

USERELATIONSHIP switches the date filter onto the clock stop date for this measure only. For September 2025 that gives 1,622 treatments against 1,800 in September 2024. Validation removals (1,095 since April 2023 in this data) have their own measure: they shorten the list without treating anyone, and a report that counts them as activity flatters the trust.

Checking the measures

DAX that parses can still be wrong, and the snapshot patterns fail quietly, with plausible numbers. What I do:

1. Compute the KPIs somewhere else. The project computes every measure in pandas from the same CSVs. The two implementations share definitions, not code.
2. Run all the measures as one query. The project’s rtt-measures.dax defines all 18 measures with DEFINE MEASURE and evaluates them by specialty. In DAX query view, query-scoped measures run without touching the model, and the CodeLens above each one can write it back when you’re happy.
3. Test the awkward cells. A month with five Sundays, a specialty with no long waiters, a card with no filters, and the wait band with a blank upper edge. Those are where each pattern above breaks.

For the model around these measures, see A star schema for hospital activity and TMDL and version control for Power BI semantic models. For NHS financial years and rolling periods, see Time intelligence for NHS financial years and rolling periods.