Modelling FIFO expiry rules in DAX

The UK Apprenticeship Levy has a deceptively simple rule: funds expire after 24 months, consumed first-in-first-out. From August 2026 that window halves to 12 months. Tell me how much of the current balance will expire next quarter.

It sounds like a SUMIF. It isn't.

The complication is that "how much of the current balance will expire" depends on which pots of levy have been drawn against, in what order, and which pot each pound of spend came from. You can't just look at the spend column. You have to allocate it.

This is the pattern I landed on after three failed attempts.

The data we're starting with

A single fact table. One row per transaction. Each Levy row is a pot of money entering the account. Each spend or transfer row is money leaving. There's no allocation column — the data doesn't tell us which pot funded which payment.

That's our job.

The wrong way

My first attempt was a calculated column on the fact table that tried to walk forward through the rows in date order, deducting spend from the oldest open pot. It worked. It also took twelve seconds to refresh on 500 rows. At a million rows it would have been geological.

DAX is not the right tool for iterating with mutable state. Stop trying.

The right way: virtual FIFO

The insight is that you don't need to know which pot funded which specific payment. You only need to know, for each pot, how much of it has been drawn by the time the report is rendered.

Here's the trick. For any given pot, the amount drawn from it is:

drained_from_this_pot = max(0, total_drain - prior_pots_capacity)
                      ^ capped at the pot's own size

Where:

total_drain = sum of every spend and transfer in the dataset
prior_pots_capacity = sum of every Levy pot dated before this one

In other words: spend fills the older pots first. Once their combined capacity is exhausted, it starts spilling into the current pot. The remainder is what's left.

The calculated table

I build a LevyPots table — one row per Levy receipt — with the expiry date computed using the policy in force at entry:

LevyPots =
ADDCOLUMNS (
    SELECTCOLUMNS (
        FILTER ( FactTransactions, FactTransactions[TransactionType] = "Levy" ),
        "EntryDate", FactTransactions[TransactionDate],
        "PotAmount", FactTransactions[Total]
    ),
    "ExpiryMonths", IF ( [EntryDate] >= DATE ( 2026, 8, 1 ), 12, 24 ),
    "ExpiryDate",   EDATE ( [EntryDate], IF ( [EntryDate] >= DATE ( 2026, 8, 1 ), 12, 24 ) )
)

Notice the inline switch: 12 months if the pot entered after the policy change, 24 months otherwise. The new rule applies to new pots only; old pots keep their old window.

The measure

Pot Remaining :=
VAR Today      = TODAY ()
VAR PotEntry   = SELECTEDVALUE ( LevyPots[EntryDate] )
VAR PotAmount  = SELECTEDVALUE ( LevyPots[PotAmount] )
VAR PotExpiry  = SELECTEDVALUE ( LevyPots[ExpiryDate] )

VAR CumCapacityToThisPot =
    CALCULATE (
        SUM ( LevyPots[PotAmount] ),
        FILTER ( ALL ( LevyPots ), LevyPots[EntryDate] <= PotEntry )
    )

VAR TotalDrain =
    CALCULATE (
        -1 * SUM ( FactTransactions[Total] ),
        FactTransactions[TransactionType] IN
            { "Monthly payment", "Completion payment", "Balance payment", "Transfers" },
        FactTransactions[TransactionDate] <= Today
    )

VAR PriorCapacity = CumCapacityToThisPot - PotAmount
VAR Drained = MIN ( PotAmount, MAX ( 0, TotalDrain - PriorCapacity ) )
VAR Remaining = PotAmount - Drained

RETURN
    IF ( PotExpiry >= Today, Remaining, 0 )

Three things to notice:

No iteration over rows. Just two aggregations against the same table, evaluated per pot in the row context. DAX is happy.
The expiry gate at the end. If the pot has already expired by today's date, we return zero — it's no longer "at risk", it's already gone.
The MIN/MAX clamp. This is the FIFO logic. MAX(0, ...) says spend hasn't reached this pot yet (no draw). MIN(PotAmount, ...) says spend has overflowed past this pot (fully drawn).

The forecast measures

Once Pot Remaining exists, the horizon measures are trivial:

Funds Expiring Next 12 Months :=
SUMX (
    FILTER (
        LevyPots,
        LevyPots[ExpiryDate] >= TODAY ()
            && LevyPots[ExpiryDate] <= EDATE ( TODAY (), 12 )
    ),
    [Pot Remaining]
)

Sum the remaining residual on every pot that's set to expire in the horizon window.

Performance reality check

This is O(N²) in the worst case — each pot's measure iterates the entire LevyPots table. On 500 rows you don't notice. On 100,000 you will.

If you need to scale, materialise the allocation as a calculated table that pre-computes Pot Remaining for every pot, then sum from that. You lose the runtime responsiveness to TODAY() but gain orders of magnitude in query performance.

What I'd change if I were doing it again

I'd build the forecast as a calculated table from day one. The measure is more elegant, but elegance doesn't survive contact with a refresh window. And I'd add a parameter table for the policy switch date — hard-coding DATE(2026,8,1) is a maintenance trap waiting to happen.

The pattern works because we stopped trying to allocate spend to specific pots and instead asked the simpler question: by the time we get to this pot, how much spend has already happened? FIFO falls out of the arithmetic.

There's a lesson here that goes wider than DAX. When a problem looks like it needs iteration, look for the closed-form expression first. The answer is usually sitting in plain sight, dressed up as a cumulative sum.

Modelling FIFO expiry rules in DAX without losing your mind.