KPI: Customer Churn Rate (%)

Customer Churn Rate (%)

Description

Customer Churn Rate represents the percentage of customers who stop using a product or service within a given time period. It is a critical KPI for subscription-based businesses, helping identify retention issues and evaluate the impact of customer success initiatives.

Visual Example

DAX Formulas

Churned Customers = 
VAR MonthStart = MIN('Date'[Date])
VAR MonthEnd   = EOMONTH(MonthStart, 0)

RETURN
CALCULATE (
    DISTINCTCOUNT (Subscriptions[CustomerID]),
    FILTER (
        Subscriptions,
        NOT ISBLANK(Subscriptions[EndDate]) &&
        Subscriptions[EndDate] >= MonthStart &&
        Subscriptions[EndDate] <= MonthEnd
    )
)

Customers at Start of Period = 
CALCULATE(
    DISTINCTCOUNT(Subscriptions[CustomerID]),
    FILTER(
        Subscriptions,
        Subscriptions[StartDate] <= MIN('Date'[Date]) &&
        (ISBLANK(Subscriptions[EndDate]) || Subscriptions[EndDate] > MIN('Date'[Date]))
    )
)

Churn Rate % = 
DIVIDE(
    [Churned Customers],
    [Customers at Start of Period]
)

SQL Statement

WITH calendar AS (
  SELECT
    DATE_TRUNC('month', FullDate)::DATE AS MonthStart,
    DATE_TRUNC('month', FullDate)::DATE + INTERVAL '1 MONTH - 1 day' AS MonthEnd
  FROM DimDate
  GROUP BY DATE_TRUNC('month', FullDate)
),
churned AS (
  SELECT
    c.MonthStart,
    COUNT(DISTINCT s.CustomerID) AS ChurnedCustomers
  FROM calendar c
  JOIN Subscriptions s
    ON s.EndDate IS NOT NULL
   AND s.EndDate BETWEEN c.MonthStart AND c.MonthEnd
  GROUP BY c.MonthStart
),
active_at_start AS (
  SELECT
    c.MonthStart,
    COUNT(DISTINCT s.CustomerID) AS CustomersAtStart
  FROM calendar c
  JOIN Subscriptions s
    ON s.StartDate <= c.MonthStart
   AND (s.EndDate IS NULL OR s.EndDate > c.MonthStart)
  GROUP BY c.MonthStart
)
SELECT
  a.MonthStart,
  a.CustomersAtStart,
  COALESCE(ch.ChurnedCustomers, 0) AS ChurnedCustomers,
  CASE
    WHEN a.CustomersAtStart = 0 THEN NULL
    ELSE ROUND(100.0 * ch.ChurnedCustomers / a.CustomersAtStart, 2)
  END AS ChurnRatePct
FROM active_at_start a
LEFT JOIN churned ch ON a.MonthStart = ch.MonthStart
ORDER BY a.MonthStart;

Usage Notes

Churned Customers are those who were active in the previous period but are no longer active in the current period.
Customers at Start of Period typically refers to customers active in the previous full period (e.g., start of the month).
Use a disconnected Date table to calculate churn month-over-month with proper filtering.
Pair churn rate with retention rate (1 - churn) and cohort analysis for deeper insights.

Downloads

Download PBIX Download Subscriptions CSV