1. Scope & Objectives

What the report delivers and the questions it answers.

The dashboard provides a comprehensive view of UK Apprenticeship Levy activity to support data-driven decision making. Specifically, it answers:

• How much levy have we received, spent, transferred, lost to expiry, and have remaining?
• How is utilisation tracking by financial year and is it improving?
• How is spend split between Legal and Business Services training pathways?
• Which months show cost spikes (typically driven by Completion Payments at end-point assessment)?
• How much of our current balance is at risk of expiring in the next 12-24 months under the new policy?

2. Business Assumptions & Rules

The rules that govern how levy transactions behave - these drive every calculation.

2.1 Transaction Type Semantics

2.2 Expiry Policy

2.3 Course Categorisation

• Legal: course name contains Paralegal, Solicitor, or Legal.
• Business Services: all other courses (HR, Project, Accounting, Customer Service, Management, Compliance, Financial Services).

2.4 Financial Year

UK FY: 1 April to 31 March. Format FY2025/26.

3. Data Model Design

Star schema: one fact, three dimensions.

Step 1 - Import & Shape Data (Power Query)

Get the raw CSV into a clean, typed fact table.

if [Course] = null then "Unallocated"
else if Text.Contains([Course],"Paralegal") or Text.Contains([Course],"Solicitor") or Text.Contains([Course],"Legal")
     then "Legal"
else "Business Services"

Step 2 - Build the Date Table

Modeling → New Table.

-- DimDate: continuous date dimension with UK FY
DimDate =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2030, 3, 31 ) ),
    "Year",        YEAR ( [Date] ),
    "MonthNum",    MONTH ( [Date] ),
    "MonthName",   FORMAT ( [Date], "MMM" ),
    "YearMonth",   FORMAT ( [Date], "yyyy-MM" ),
    "FYStartYear", IF ( MONTH ( [Date] ) >= 4, YEAR ( [Date] ), YEAR ( [Date] ) - 1 ),
    "FY",
        "FY" & IF ( MONTH ( [Date] ) >= 4, YEAR ( [Date] ), YEAR ( [Date] ) - 1 )
              & "/" & RIGHT ( IF ( MONTH ( [Date] ) >= 4, YEAR ( [Date] ) + 1, YEAR ( [Date] ) ), 2 ),
    "FYMonthIndex",
        MOD ( MONTH ( [Date] ) - 4 + 12, 12 ) + 1,
    "FYQuarter",
        "Q" & ( QUOTIENT ( MOD ( MONTH ( [Date] ) - 4 + 12, 12 ), 3 ) + 1 )
)

Step 3 - Course Category & Transaction Type

DimCourseCategory =
DATATABLE (
    "CourseCategory", STRING, "CategoryOrder", INTEGER,
    {
        { "Legal", 1 },
        { "Business Services", 2 },
        { "Unallocated", 3 }
    }
)

DimTransactionType =
DATATABLE (
    "TransactionType", STRING, "Bucket", STRING, "SignConvention", STRING,
    {
        { "Levy",               "Income",     "Positive" },
        { "Levy adjustment",    "Adjustment", "Mixed" },
        { "Monthly payment",    "Spend",      "Negative" },
        { "Completion payment", "Spend",      "Negative" },
        { "Balance payment",    "Spend",      "Negative" },
        { "Transfers",          "Transfer",   "Negative" },
        { "Expired levy",       "Expired",    "Negative" }
    }
)

Step 4 - Create Relationships

Step 5 - Core Measures

The seven headline KPIs. Drop all measures into a hidden _Measures table.

-- 1. Total Levy In
Total Levy In :=
CALCULATE ( SUM ( FactTransactions[Total] ),
    FactTransactions[TransactionType] = "Levy" )

-- 2. Total Levy Adjustments
Total Levy Adjustments :=
CALCULATE ( SUM ( FactTransactions[Total] ),
    FactTransactions[TransactionType] = "Levy adjustment" )

-- 3. Total Levy Spend
Total Levy Spend :=
CALCULATE ( -1 * SUM ( FactTransactions[Total] ),
    FactTransactions[TransactionType] IN
        { "Monthly payment", "Completion payment", "Balance payment" } )

-- 4-5. Category splits
Legal Spend :=
CALCULATE ( [Total Levy Spend], FactTransactions[CourseCategory] = "Legal" )

Business Services Spend :=
CALCULATE ( [Total Levy Spend], FactTransactions[CourseCategory] = "Business Services" )

-- 6. Transfers
Transfer Funds :=
CALCULATE ( -1 * SUM ( FactTransactions[Total] ),
    FactTransactions[TransactionType] = "Transfers" )

-- 7. Expired
Expired Funds :=
CALCULATE ( -1 * SUM ( FactTransactions[Total] ),
    FactTransactions[TransactionType] = "Expired levy" )

-- 8. Remaining Balance
Remaining Balance :=
VAR LevyIn  = [Total Levy In] + [Total Levy Adjustments]
VAR Outflow = [Total Levy Spend] + [Transfer Funds] + [Expired Funds]
RETURN LevyIn - Outflow

-- 9. % Utilisation
% Utilisation :=
DIVIDE ( [Total Levy Spend], [Total Levy In] + [Total Levy Adjustments], 0 )

-- 10. % Leakage
% Leakage :=
DIVIDE ( [Expired Funds] + [Transfer Funds], [Total Levy In] + [Total Levy Adjustments], 0 )

Step 6 - Trend & Variance Measures

-- Cumulative measures
Cumulative Levy In :=
CALCULATE ( [Total Levy In],
    FILTER ( ALL ( DimDate ), DimDate[Date] <= MAX ( DimDate[Date] ) ) )

Cumulative Spend :=
CALCULATE ( [Total Levy Spend],
    FILTER ( ALL ( DimDate ), DimDate[Date] <= MAX ( DimDate[Date] ) ) )

Cumulative Expired :=
CALCULATE ( [Expired Funds],
    FILTER ( ALL ( DimDate ), DimDate[Date] <= MAX ( DimDate[Date] ) ) )

Running Balance :=
CALCULATE ( [Remaining Balance],
    FILTER ( ALL ( DimDate ), DimDate[Date] <= MAX ( DimDate[Date] ) ) )

-- Completion Spike Flag (cohort throughput signal)
Completion Spike Flag :=
VAR ThisMonth =
    CALCULATE ( -1 * SUM ( FactTransactions[Total] ),
        FactTransactions[TransactionType] = "Completion payment" )
VAR Avg6 =
    CALCULATE (
        AVERAGEX (
            VALUES ( DimDate[YearMonth] ),
            CALCULATE ( -1 * SUM ( FactTransactions[Total] ),
                FactTransactions[TransactionType] = "Completion payment" )
        ),
        DATESINPERIOD ( DimDate[Date], MAX ( DimDate[Date] ), -6, MONTH )
    )
RETURN IF ( ThisMonth > 1.5 * Avg6 && ThisMonth > 0, 1, 0 )

-- Year on year
Spend YoY % :=
VAR Cur = [Total Levy Spend]
VAR Prv = CALCULATE ( [Total Levy Spend], DATEADD ( DimDate[Date], -1, YEAR ) )
RETURN DIVIDE ( Cur - Prv, Prv, BLANK () )

-- FY status
FY Utilisation Status :=
VAR U = [% Utilisation]
RETURN SWITCH ( TRUE (),
    U >= 0.40, "Healthy",
    U >= 0.25, "Watch",
    "Low" )

Step 7 - Expiry Forecast Model

7.1 Calculated table - LevyPots

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

7.2 FIFO consumption 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 DrainedFromThisPot =
    MIN ( PotAmount, MAX ( 0, TotalDrain - PriorCapacity ) )
VAR Remaining = PotAmount - DrainedFromThisPot
RETURN IF ( PotExpiry >= Today, Remaining, 0 )

7.3 Forecast horizons

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

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

Forecast Expiring This Period :=
SUMX (
    FILTER ( LevyPots,
        LevyPots[ExpiryDate] >= MIN ( DimDate[Date] )
            && LevyPots[ExpiryDate] <= MAX ( DimDate[Date] )
            && LevyPots[ExpiryDate] >= TODAY () ),
    [Pot Remaining] )

Step 8 - Page 1: Executive Overview

Step 9 - Page 2: Trend Analysis

Step 10 - Page 3: Expiry Forecast

Step 11 - Page 4: Legal vs Business Services

Metric Library - Business & Calculation Logic

Reference for every metric. Use as the canonical specification when migrating to production data.

Governance, Refresh & QA

Refresh

• Frequency: Monthly, aligned with HMRC levy account update (~22nd).
• Source: Manage Apprenticeships service CSV export.
• Gateway: Personal for prototype; on-prem with RLS for production.

Data Quality Checks

1. Row count vs source CSV - exact match.
2. Sum of Total reconciles to HMRC statement.
3. No null TransactionDate or Total.
4. TransactionType values within the seven known types.
5. Remaining Balance matches HMRC digital account within £1.
6. LevyPots row count = count of "Levy" rows in fact.

Performance

• Star schema; sub-second queries under 1M rows.
• Pot Remaining is O(N²) worst case - materialise FIFO as a calculated table for > 100k rows.
• Avoid bidirectional filters.

Security

• Apprentice names are PII - RLS so only HR Ops sees individual names.
• Mask Apprentice column at report layer for non-HR audiences.