How to calculate churn rate (formula, DAX, SQL and Excel)

Short answer: customer churn rate is the customers you lost in a period divided by the customers you had at the start of it, times 100.

If you started March with 800 customers and 20 of them cancelled during March, your monthly churn rate is 20 ÷ 800 × 100 = 2.5%.

#What does a worked example look like?

Churn rate = 20 ÷ 800 = 2.5%. The 60 new customers do not enter the calculation, and that is the first rule.

#Which mistakes make churn lie?

1. Counting new customers in the denominator. Dividing by 860 (start plus new) gives 2.3% and hides churn when you grow fast. Divide by the customers at the start.
2. Counting customers who joined and left in the same month. They were never in the starting number, so they do not belong in the lost number either. Track them separately: early cancellations are an onboarding problem.
3. Counting a downgrade as churn. A customer who moves to a cheaper plan has not left. That belongs in revenue churn (below), not customer churn.
4. Comparing annual plans month by month. Annual customers can only leave once a year, so monthly churn looks tiny for eleven months and then spikes. Report annual-plan churn per renewal cohort, or annually.

#How do monthly and annual churn relate?

Monthly churn does not multiply by 12 into annual churn, because each month you lose a share of a smaller base. The correct conversion:

At 2.5% a month, that is 1 − 0.975¹² = 26.2% a year, not 30%.

#What about revenue churn and net revenue retention?

Customer churn (also called logo churn) counts customers. Revenue churn counts money, so a big customer leaving weighs more than a small one:

Net revenue retention (NRR) adds the expansion of the customers who stayed: upgrades, more seats, add-ons. For the customers you had a year ago:

NRR above 100% means existing customers grow faster than you lose them: you would grow even with no new sales.

#What is a good churn rate?

Benchmarks vary a lot by price point and market. Your own trend matters more: is churn this quarter lower than last quarter?

#How do you calculate it in Power BI with DAX?

With a Subscriptions table that flags who was active at the start of the period and who churned in it:

Customer Churn % =
VAR StartCust = CALCULATE ( DISTINCTCOUNT ( Subscriptions[Customer ID] ), Subscriptions[Active At Start] = TRUE () )
VAR Lost      = CALCULATE ( DISTINCTCOUNT ( Subscriptions[Customer ID] ), Subscriptions[Churned In Period] = TRUE () )
RETURN DIVIDE ( Lost, StartCust )

DIVIDE returns blank instead of an error for a period with no starting customers. Format the measure as a percentage.

#How do you calculate it in SQL?

From a table of active customers per month, a customer churned when they were active one month and not the next:

WITH m AS (
  SELECT month, customer_id FROM subscriptions WHERE status = 'active' GROUP BY 1, 2
)
SELECT a.month + INTERVAL '1 month' AS month,
       COUNT(*) FILTER (WHERE b.customer_id IS NULL) * 100.0 / COUNT(*) AS churn_pct
FROM m a
LEFT JOIN m b ON b.customer_id = a.customer_id AND b.month = a.month + INTERVAL '1 month'
GROUP BY 1
ORDER BY 1;

This is PostgreSQL; other databases write the interval and the FILTER clause differently.

#How do you calculate it in Excel?

With the counts in named cells:

=CustomersLost / CustomersAtStart

Format the cell as a percentage. For the annual rate: =1-(1-MonthlyChurn)^12.

#How should you show it?

A line chart of monthly churn, with a three-month average and a target line, shows whether it is getting better. A cohort chart (the share of each signup month still active after 1, 2, 3 months) shows when customers leave. A bar chart by plan or segment shows who leaves.

KPI Atlas has customer churn rate and the KPIs to read with it (revenue churn, net revenue retention, retention rate and customer lifetime value), each with its formula, DAX, SQL and Excel to copy, a benchmark, the common mistakes, and its best charts drawn.