// Complaints MI Pack: measures
// Generated by Blueprint BI

// ===== Volumes =====
// Complaints received in the period, by received date. (format: #,0)
[Complaints Received] =
    DISTINCTCOUNT ( FactComplaint[ComplaintID] )

// Complaints given a final response in the period. (format: #,0)
[Complaints Resolved] =
    CALCULATE ( DISTINCTCOUNT ( FactComplaint[ComplaintID] ), USERELATIONSHIP ( FactComplaint[FinalResponseDateKey], DimDate[DateKey] ) )

// Complaints without a final response at the last date in context. (format: #,0)
[Open Complaints] =
    VAR AsAtDate = MIN ( MAX ( DimDate[Date] ), TODAY () )
    VAR AsAtKey = YEAR ( AsAtDate ) * 10000 + MONTH ( AsAtDate ) * 100 + DAY ( AsAtDate )
    RETURN CALCULATE ( DISTINCTCOUNT ( FactComplaint[ComplaintID] ), REMOVEFILTERS ( DimDate ), FactComplaint[ReceivedDateKey] <= AsAtKey, FactComplaint[FinalResponseDateKey] > AsAtKey )

// ===== Time Intelligence =====
// Complaints received in the same period last year. (format: #,0)
[Complaints Received PY] =
    CALCULATE ( [Complaints Received], SAMEPERIODLASTYEAR ( DimDate[Date] ) )

// 8-week resolution rate in the same period last year. (format: 0.0%)
[Resolved Within 8 Weeks % PY] =
    CALCULATE ( [Resolved Within 8 Weeks %], SAMEPERIODLASTYEAR ( DimDate[Date] ) )

// Uphold rate in the same period last year. (format: 0.0%)
[Uphold Rate % PY] =
    CALCULATE ( [Uphold Rate %], SAMEPERIODLASTYEAR ( DimDate[Date] ) )

// Complaints per 1,000 customers in the same period last year. (format: 0.00)
[Complaints per 1,000 Customers PY] =
    CALCULATE ( [Complaints per 1,000 Customers], SAMEPERIODLASTYEAR ( DimDate[Date] ) )

// Ombudsman referral rate in the same period last year. (format: 0.0%)
[Ombudsman Referral Rate % PY] =
    CALCULATE ( [Ombudsman Referral Rate %], SAMEPERIODLASTYEAR ( DimDate[Date] ) )

// ===== Targets =====
// Firm-wide monthly complaints received target. (format: #,0)
[Complaints Received Monthly Target] =
    1200

// Monthly target times months in context; blank when sliced below firm level. (format: #,0)
[Complaints Received Target] =
    VAR Months = COUNTROWS ( VALUES ( DimDate[YearMonthNumber] ) )
    VAR IsSliced = ISFILTERED ( DimProductLine ) || ISFILTERED ( DimChannel ) || ISFILTERED ( DimRootCause ) || ISFILTERED ( DimTeam ) || ISFILTERED ( DimHandler )
    RETURN IF ( IsSliced || Months = 0, BLANK (), [Complaints Received Monthly Target] * Months )

// Percentage by which received complaints differ from target. (format: +0.0%;-0.0%;0.0%)
[Complaints Received vs Target %] =
    VAR Tgt = [Complaints Received Target]
    RETURN IF ( ISBLANK ( Tgt ) || Tgt <= 0, BLANK (), DIVIDE ( [Complaints Received] - Tgt, Tgt ) )

// Target share resolved within 8 weeks. (format: 0.0%)
[Resolved Within 8 Weeks Target] =
    0.95

// Percentage-point gap to the 8-week target. (format: +0.0%;-0.0%;0.0%)
[Resolved Within 8 Weeks Variance] =
    VAR Actual = [Resolved Within 8 Weeks %]
    RETURN IF ( ISBLANK ( Actual ), BLANK (), Actual - [Resolved Within 8 Weeks Target] )

// Target maximum uphold rate. (format: 0.0%)
[Uphold Rate Target] =
    0.35

// Percentage-point gap to the uphold rate target. (format: +0.0%;-0.0%;0.0%)
[Uphold Rate Variance] =
    VAR Actual = [Uphold Rate %]
    RETURN IF ( ISBLANK ( Actual ), BLANK (), Actual - [Uphold Rate Target] )

// Target maximum monthly complaints per 1,000 customers. (format: 0.00)
[Complaints per 1,000 Target] =
    2.5

// Gap to the complaints per 1,000 target. (format: +0.00;-0.00;0.00)
[Complaints per 1,000 Variance] =
    VAR Actual = [Complaints per 1,000 Customers]
    RETURN IF ( ISBLANK ( Actual ), BLANK (), Actual - [Complaints per 1,000 Target] )

// Target maximum Ombudsman referral rate. (format: 0.0%)
[Ombudsman Referral Rate Target] =
    0.05

// Percentage-point gap to the referral rate target. (format: +0.0%;-0.0%;0.0%)
[Ombudsman Referral Rate Variance] =
    VAR Actual = [Ombudsman Referral Rate %]
    RETURN IF ( ISBLANK ( Actual ), BLANK (), Actual - [Ombudsman Referral Rate Target] )

// ===== Timeliness =====
// Share of final responses sent within 56 days of receipt. (format: 0.0%)
[Resolved Within 8 Weeks %] =
    VAR Resolved = [Complaints Resolved]
    VAR Within8 = CALCULATE ( [Complaints Resolved], FactComplaint[DaysToFinalResponse] <= 56 )
    RETURN IF ( Resolved > 0, DIVIDE ( Within8, Resolved ) )

// Open complaints older than 8 weeks at the as-at date. (format: #,0)
[Open Past 8 Weeks] =
    VAR AsAtDate = MIN ( MAX ( DimDate[Date] ), TODAY () )
    VAR AsAtKey = YEAR ( AsAtDate ) * 10000 + MONTH ( AsAtDate ) * 100 + DAY ( AsAtDate )
    VAR Cutoff = AsAtDate - 56
    VAR CutoffKey = YEAR ( Cutoff ) * 10000 + MONTH ( Cutoff ) * 100 + DAY ( Cutoff )
    RETURN CALCULATE ( DISTINCTCOUNT ( FactComplaint[ComplaintID] ), REMOVEFILTERS ( DimDate ), FactComplaint[ReceivedDateKey] < CutoffKey, FactComplaint[FinalResponseDateKey] > AsAtKey )

// ===== Outcomes =====
// Fully or partially upheld complaints as a share of resolved, excluding withdrawn. (format: 0.0%)
[Uphold Rate %] =
    VAR Resolved = CALCULATE ( [Complaints Resolved], FactComplaint[Outcome] <> "Withdrawn" )
    VAR Upheld = CALCULATE ( [Complaints Resolved], FactComplaint[IsUpheld] = TRUE () )
    RETURN IF ( Resolved > 0, DIVIDE ( Upheld, Resolved ) )

// Redress in GBP on upheld complaints, by final response date. (format: £#,0)
[Redress Paid] =
    CALCULATE ( SUM ( FactComplaint[RedressAmount] ), USERELATIONSHIP ( FactComplaint[FinalResponseDateKey], DimDate[DateKey] ), FactComplaint[IsUpheld] = TRUE () )

// ===== Customers =====
// Active customers at the latest month end in context. (format: #,0)
[Active Customers] =
    CALCULATE ( SUM ( FactActiveCustomers[ActiveCustomers] ), LASTNONBLANK ( DimDate[Date], CALCULATE ( SUM ( FactActiveCustomers[ActiveCustomers] ) ) ) )

// Average monthly complaints per 1,000 active customers; blank below product-line level. (format: 0.00)
[Complaints per 1,000 Customers] =
    IF (
        ISFILTERED ( DimChannel ) || ISFILTERED ( DimRootCause ) || ISFILTERED ( DimTeam ) || ISFILTERED ( DimHandler ),
        BLANK (),
        AVERAGEX (
            VALUES ( DimDate[YearMonthNumber] ),
            VAR Cust = [Active Customers]
            RETURN IF ( Cust > 0, DIVIDE ( [Complaints Received], Cust ) * 1000 )
        )
    )

// ===== Ombudsman =====
// Complaints referred to the Financial Ombudsman Service in the period. (format: #,0)
[FOS Referrals] =
    CALCULATE ( DISTINCTCOUNT ( FactComplaint[ComplaintID] ), USERELATIONSHIP ( FactComplaint[FOSReferredDateKey], DimDate[DateKey] ) )

// Ombudsman referrals as a share of final responses in the period. (format: 0.0%)
[Ombudsman Referral Rate %] =
    VAR Resolved = [Complaints Resolved]
    RETURN IF ( Resolved > 0, DIVIDE ( [FOS Referrals], Resolved ) )

// ===== Labels =====
// Selected period label for page subtitles. (format: Text)
[Selected Period Label] =
    VAR MinD = MIN ( DimDate[Date] )
    VAR MaxD = MIN ( MAX ( DimDate[Date] ), TODAY () )
    RETURN IF ( FORMAT ( MinD, "yyyymm" ) = FORMAT ( MaxD, "yyyymm" ), FORMAT ( MaxD, "MMMM yyyy" ), FORMAT ( MinD, "d MMM yyyy" ) & " to " & FORMAT ( MaxD, "d MMM yyyy" ) )

// ===== RAG =====
// 1 Green (≤ 1,200), 2 Amber (≤ 1,320), 3 Red (> 1,320). Drives conditional formatting for Complaints Received. (format: 0)
[Complaints Received RAG] =
    VAR _Value = [Complaints Received]
    RETURN
        SWITCH (
            TRUE (),
            ISBLANK ( _Value ), BLANK (),
            _Value <= 1200, 1,    -- Green
            _Value <= 1320, 2,    -- Amber
            3                    -- Red
        )

// 1 Green (≥ 95.0%), 2 Amber (≥ 90.0%), 3 Red (< 90.0%). Drives conditional formatting for Resolved Within 8 Weeks %. (format: 0)
[Resolved Within 8 Weeks RAG] =
    VAR _Value = [Resolved Within 8 Weeks %]
    RETURN
        SWITCH (
            TRUE (),
            ISBLANK ( _Value ), BLANK (),
            _Value >= 0.95, 1,    -- Green
            _Value >= 0.9, 2,    -- Amber
            3                    -- Red
        )

// 1 Green (≤ 35.0%), 2 Amber (≤ 40.0%), 3 Red (> 40.0%). Drives conditional formatting for Uphold Rate %. (format: 0)
[Uphold Rate RAG] =
    VAR _Value = [Uphold Rate %]
    RETURN
        SWITCH (
            TRUE (),
            ISBLANK ( _Value ), BLANK (),
            _Value <= 0.35, 1,    -- Green
            _Value <= 0.4, 2,    -- Amber
            3                    -- Red
        )

// 1 Green (≤ 2.50), 2 Amber (≤ 3.00), 3 Red (> 3.00). Drives conditional formatting for Complaints per 1,000 Customers. (format: 0)
[Complaints per 1,000 Customers RAG] =
    VAR _Value = [Complaints per 1,000 Customers]
    RETURN
        SWITCH (
            TRUE (),
            ISBLANK ( _Value ), BLANK (),
            _Value <= 2.5, 1,    -- Green
            _Value <= 3, 2,    -- Amber
            3                    -- Red
        )

// 1 Green (≤ 5.0%), 2 Amber (≤ 7.0%), 3 Red (> 7.0%). Drives conditional formatting for Ombudsman Referral Rate %. (format: 0)
[Ombudsman Referral Rate RAG] =
    VAR _Value = [Ombudsman Referral Rate %]
    RETURN
        SWITCH (
            TRUE (),
            ISBLANK ( _Value ), BLANK (),
            _Value <= 0.05, 1,    -- Green
            _Value <= 0.07, 2,    -- Amber
            3                    -- Red
        )
