Skip to content
Tutorials Center
My learning

Topics

Lesson 51 of 56 ยท Projects

Project: Customer Churn Analysis

Build a customer churn report in Power BI: churn rate and MRR churn measures, plan and tenure segments, cohort retention and key influencers, verified in pandas.

Every subscription business watches one number closely: how many customers leave. A churn report answers it, and then goes further: who leaves, how soon, which plan loses the most, and which customers are likely to leave next. The reasons are practical. Winning a new customer usually costs far more than keeping an existing one, and a small monthly leak compounds into a large annual loss.

In this project you design a churn dashboard from scratch. You model customers and dates, write the measures (churned customers, churn rate, revenue churn, retention by cohort), choose the visuals, and use the Key influencers visual to explore why customers leave. Power BI is a GUI tool and cannot run on this site, so the steps are described and the DAX is read-only. The runnable examples are pandas stand-ins on a synthetic subscription table. Each one computes the same figures your report should show, and the finished report must match them.

The questions

What the report answers

How many leave? Churned customers and churn rate by month.
What does it cost? Revenue lost to churn each month.
Who leaves? Plan, region and tenure segments.
When do they leave? Retention by signup cohort.
1 / 2

Defining churn

The most important decision is the definition, because three different numbers hide behind the word:

  • Logo churn (customer churn): customers lost divided by customers at the start of the period.
  • Revenue churn: monthly recurring revenue (MRR) lost divided by MRR at the start. It is lower than logo churn when small plans leave more than large ones.
  • Net revenue churn also subtracts upgrades and expansion from existing customers. We do not model upgrades here.

Write the definition on the report. "Churn rate" without a denominator is not a number. We use the opening base: only customers who were active on the last day of the previous month are in the denominator, and only their cancellations are in the numerator.

The model

A Customers table with one row per customer: CustomerID, Signup Date, Cancel Date (blank for active customers), Plan, Region, Monthly Fee. A Date table (see Date Tables and Time Intelligence Setup). Relate 'Date'[Date] to Customers[Signup Date] as the active relationship and to Customers[Cancel Date] as an inactive one, the role-playing dates pattern from Relationships, Cardinality and Filter Direction. Add two calculated columns to Customers:

DAX
Is Churned = NOT ISBLANK ( Customers[Cancel Date] )

Tenure Months =
DATEDIFF (
    Customers[Signup Date],
    COALESCE ( Customers[Cancel Date], DATE ( 2026, 6, 30 ) ),
    MONTH
)

Tenure Band =
SWITCH (
    TRUE ( ),
    Customers[Tenure Months] < 3,  "0-2 months",
    Customers[Tenure Months] < 6,  "3-5 months",
    Customers[Tenure Months] < 12, "6-11 months",
    "12+ months"
)

The report date DATE(2026, 6, 30) should come from a parameter or from MAX('Date'[Date]) in a measure in a real project, so the report does not need editing every month. COALESCE is explained in DAX Logical and Information Functions and DATEDIFF in DAX Date and Time Functions.

The measures

DAX
Customers = COUNTROWS ( Customers )

Customers at Start =
VAR StartDate = MIN ( 'Date'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( Customers ),
        REMOVEFILTERS ( 'Date' ),
        Customers[Signup Date] < StartDate,
        ISBLANK ( Customers[Cancel Date] ) || Customers[Cancel Date] >= StartDate
    )

Churned Customers =
VAR StartDate = MIN ( 'Date'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( Customers ),
        USERELATIONSHIP ( Customers[Cancel Date], 'Date'[Date] ),
        Customers[Signup Date] < StartDate
    )

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

MRR at Start =
VAR StartDate = MIN ( 'Date'[Date] )
RETURN
    CALCULATE (
        SUM ( Customers[Monthly Fee] ),
        REMOVEFILTERS ( 'Date' ),
        Customers[Signup Date] < StartDate,
        ISBLANK ( Customers[Cancel Date] ) || Customers[Cancel Date] >= StartDate
    )

Churned MRR =
VAR StartDate = MIN ( 'Date'[Date] )
RETURN
    CALCULATE (
        SUM ( Customers[Monthly Fee] ),
        USERELATIONSHIP ( Customers[Cancel Date], 'Date'[Date] ),
        Customers[Signup Date] < StartDate
    )

Revenue Churn % = DIVIDE ( [Churned MRR], [MRR at Start] )

How they work:

  • Customers at Start counts customers who had signed up before the first day of the selected period and had not yet cancelled. REMOVEFILTERS('Date') is essential: without it the active relationship on Signup Date would filter the table to people who signed up in the period, which is the opposite of what we want. The details of such overrides are in CALCULATE and Filter Context.
  • Churned Customers switches to the inactive Cancel Date relationship, so the date filter selects cancellations in the period, and keeps only customers who were in the opening base (Signup Date < StartDate). Customers who joined and left in the same month are a separate edge case and are not counted in the rate.
  • Churn Rate divides one by the other with DIVIDE. For a quarter or year, MIN('Date'[Date]) is the first day of the period, so the rate becomes the share of the opening customers lost during that whole period, which is a different (larger) number than a monthly rate. Label it accordingly.
  • The MRR versions repeat the logic with SUM(Monthly Fee).

Check them before you trust them

Build the same numbers in pandas, and make sure your report agrees. The stand-in generates 400 synthetic customers and computes, for each month, the opening base, new customers, cancellations, closing count, churn rate and revenue churn, with a roll-forward check: opening plus new minus churned must equal closing.

Python Stand-in: monthly churn, revenue churn and the roll-forward check

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Every month passes the roll-forward check. Notice that revenue churn is lower than logo churn in every month: the customers who leave are mostly on the small Basic plan, so fewer dollars leave than customers. That gap is itself a finding, and it is the reason to report both.

The report pages

  1. Overview. Cards for Customers, Churned Customers, Churn Rate and Revenue Churn % (see Conditional Formatting, KPIs and Cards for KPI formatting); a line chart of Churn Rate by month with a constant line for the target; a clustered column chart of new and churned customers by month.
  2. Segments. A bar chart of churn share by Plan, a matrix of plan by region, and a column chart of cancellations by Tenure Band. A slicer for plan and one for region.
  3. Cohorts. A matrix of retention by signup cohort (explained below).
  4. Why customers leave. The Key influencers visual, described below.
  5. At-risk customers. A table of active customers with the risk factors, reached by drill-through (Drill-through, Tooltips, Bookmarks and Buttons).

Give every chart a title that states the point, such as "Churn peaked in May", and use the same period filter on every page (Filters, Slicers and Sync).

Who leaves, and when

Python Stand-in: churn by plan, tenure at cancellation and cohort retention

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Basic customers churn at more than double the rate of Premium customers. By tenure, 40 customers left in the first three months, 42 in the next three, 40 across the following six months (half the monthly pace) and only 8 after a year, so risk is highest early in a customer's life. The cohort table reads left to right: each row is a signup quarter and each column the share still active after 3, 6 and 12 months. Blank cells are not zeros: those cohorts have not reached that age yet, which is why a cohort measure needs a guard. In DAX, return BLANK() when the cohort's last signup date plus n months is after the report date, otherwise young cohorts look as if they never churned.

The cohort matrix in DAX

Create a small disconnected table Month Index (with GENERATESERIES(0, 12, 1)) so the matrix has columns for 0 to 12 months, and a Signup Cohort column on Customers (FORMAT(Customers[Signup Date], "yyyy-MM")). Then:

DAX
Retention % =
VAR N = SELECTEDVALUE ( 'Month Index'[Index] )
VAR LastSignup = MAX ( Customers[Signup Date] )
VAR Base = COUNTROWS ( Customers )
VAR Retained =
    CALCULATE (
        COUNTROWS ( Customers ),
        Customers[Tenure Months] >= N || NOT Customers[Is Churned]
    )
RETURN
    IF ( EDATE ( LastSignup, N ) > MAX ( 'Date'[Date] ), BLANK ( ), DIVIDE ( Retained, Base ) )

Use conditional formatting as a heat map on the matrix values, so the pattern (a slope down and to the right) is visible at a glance. FORMAT produces text, so sort the cohort column with a numeric key, as warned in DAX Text Functions.

Why do they leave? Key influencers

On the Why page add a Key influencers visual. Set Analyze to Is Churned (what the outcome is: True) and Explain by to Plan, Region, Tenure Band and any ticket or usage columns you have. The visual lists factors, such as "when Plan is Basic, churn is 1.9x more likely", and the Top segments tab lists groups of customers with the highest churn and their size. Read it with the cautions from AI Visuals: Key Influencers, Decomposition Tree and Smart Narrative: check segment sizes, and remember that influence is not cause. For example, many support tickets may be a symptom of an unhappy customer, not the reason they leave.

From analysis to action

A churn report is only useful if it triggers something. Common actions: contact the at-risk segment before renewal, change the onboarding for the first three months, offer an annual plan to Basic customers, or review the price of the plan with the highest churn. Add a data alert on a service dashboard tile so the team is told when the monthly churn rate crosses its threshold (Power BI Service).

Final checks

  • Each measure has a description that states its denominator.
  • Churned plus remaining customers equal the total, and the roll-forward check holds in a table visual.
  • The report works when you select a quarter, a year and a single plan.
  • No personal data (names, emails) is in the model unless a role needs it, and row-level security is tested with View as (Row-Level Security).

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why does Customers at Start use REMOVEFILTERS('Date')?

    The opening base is made of customers who signed up before the period. The active relationship would filter to people who signed up during it, so the filter must be removed.
  2. 2. Revenue churn is lower than logo churn. What is the likely reason?

    When small plans churn the most, the share of customers lost is larger than the share of revenue lost.
  3. 3. A cohort cell is blank in the retention matrix. What does it mean?

    A young cohort cannot be observed at month 12. Showing zero or 100 percent would be wrong, so the measure returns blank.
  4. 4. What does the roll-forward check prove?

    If the three movements do not add up to the closing count, a date, a relationship or a filter is wrong.
  5. 5. Which setting makes the Key influencers visual study customer churn?

    Analyze holds the outcome and Explain by holds the factors; an ID has no explanatory value.

Summary

  • Define churn first: logo churn and revenue churn have different numerators and denominators, and both use the opening base.
  • Model signup and cancel dates with one active and one inactive relationship, and use USERELATIONSHIP and REMOVEFILTERS in the measures.
  • Segment by plan, region and tenure, and use cohort retention with a guard for cohorts that are too young.
  • Verify with a roll-forward check and a pandas recomputation, then use Key influencers to explore reasons, and treat the findings as leads, not causes.

Next: Project: E-commerce Analytics Dashboard.