Apprenticeship levy activity dashboard.
A board-ready view of UK apprenticeship levy activity - utilisation, spend by pathway, and a FIFO expiry forecast that survives the August 2026 policy change.
The brief
HR leadership wanted a comprehensive view of apprenticeship levy activity that could answer one question on demand: are we spending our levy well, and if not, where is it going? The current view was a CSV download once a month and a lot of squinting.
The dashboard had to cover:
- Total spend, expired funds, transfers, remaining balance
- Utilisation by financial year - against a target
- Split of spend between Legal and Business Services pathways
- Trend signals - especially cost spikes that flag new intakes
- A forward-looking forecast of which funds were at risk of expiring
The complication
The expiry rule is changing. Today, levy funds expire 24 months after they enter the account. From August 2026, that drops to 12 months. Any forecast had to handle both regimes and apply the right one to the right pot of money.
Funds entering before 1 Aug 2026 expire after 24 months. Funds entering on or after 1 Aug 2026 expire after 12 months. FIFO consumption.
How I approached it
1. Define the business logic before opening Power BI
I wrote down what each transaction type means, what its sign convention is, and which bucket it belongs in. This became the rulebook every measure was built against - and the document HR leadership signed off before any visuals existed.
2. Build a clean star schema
One fact table for transactions, three dimensions for date, transaction type, and course category. No bidirectional filters. The FY logic lives in the date table so every measure inherits it automatically.
3. Layer the measures
Seven headline KPIs, then trend and variance measures on top, then forecast measures last. Each layer only depends on the one below - so when the data refreshes, the chain stays predictable.
4. Model the FIFO expiry in DAX
The hardest part. I modelled each levy receipt as a "pot" with its own expiry date based on the policy in force when it arrived. Then I used a virtual FIFO consumption pattern: cumulative pot capacity minus prior pots' capacity tells me how much of each pot has been drawn. The residual on any pot whose expiry is still in the future is "at risk".
Pot Remaining :=
VAR PotEntry = SELECTEDVALUE ( LevyPots[EntryDate] )
VAR PotAmount = SELECTEDVALUE ( LevyPots[PotAmount] )
VAR PotExpiry = SELECTEDVALUE ( LevyPots[ExpiryDate] )
VAR CumCapacity =
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" } )
VAR PriorCapacity = CumCapacity - PotAmount
VAR Drained = MIN ( PotAmount, MAX ( 0, TotalDrain - PriorCapacity ) )
RETURN IF ( PotExpiry >= TODAY (), PotAmount - Drained, 0 )The result
- One dashboard, four pages: Executive Overview, Trend Analysis, Expiry Forecast, Legal vs Business Services split.
- 16 governed DAX measures with documented business logic.
- A forecast view that shows exactly how much money will expire in each upcoming month, colour-coded so the next 12 months pop visually.
- A completion-payment spike flag that highlights months where end-point assessments cluster - usually a precursor to a new intake.
What I'd do differently next time
- Materialise the FIFO allocation as a calculated table from the start - the measure approach is elegant but O(N²) at scale.
- Add row-level security earlier - apprentice names are PII and should never be visible outside HR Ops.
- Build a small pytest-style validation harness so monthly refreshes auto-flag if the remaining balance ever disagrees with the HMRC statement.
Takeaway
The boring half of building dashboards is documenting the business logic. The interesting half is choosing a model that survives a policy change two years out. Doing both is what makes the difference between a chart and a tool people actually use.