How to calculate monthly churn in DAX: step by step

João Barros 23 de July de 2026 3 min read

This tutorial shows how to calculate monthly churn in DAX to find out how many customers were lost between months and what the churn rate is. Calculating monthly churn in DAX is useful for product, marketing and reporting because it helps measure retention and identify issues early.

Prerequisites

Power BI Desktop (or another engine with DAX support).
Transactions table with at least the columns Transactions[CustomerID] and Transactions[Date].
Date (calendar) table linked to Transactions[Date] and marked as Date table.
Basic knowledge of DAX and visualizations (matrix/line chart).

Step 1: Create and mark the date table

Time calculations require a continuous date table marked as Date table. Without it, PREVIOUSMONTH and other Time Intelligence functions will not work correctly.

Date =
VAR MinD = MIN( Transactions[Date] )
VAR MaxD = MAX( Transactions[Date] )
RETURN
ADDCOLUMNS(
  CALENDAR( MinD, MaxD ),
  "Year", YEAR([Date]),
  "MonthNumber", MONTH([Date])
)

Step 2: Active customers measure (by period)

Start with a measure that counts active customers in the filter context (for example, by month in the matrix row).

Clientes Activos = DISTINCTCOUNT( Transactions[CustomerID] )

Step 3: Customers in the previous month

Use PREVIOUSMONTH to get the number of customers in the month prior to the current context. This measure serves as the denominator when calculating the churn rate.

Clientes Mês Anterior =
CALCULATE(
  [Clientes Activos],
  PREVIOUSMONTH( 'Date'[Date] )
)

Step 4: Calculate lost customers (churn) in DAX

Here we use sets of customer IDs: customers from the previous month minus customers from the current month. EXCEPT returns the IDs that left the current set.

Clientes Perdidos =
VAR PrevCustomers =
  CALCULATETABLE( VALUES( Transactions[CustomerID] ), PREVIOUSMONTH('Date'[Date]) )
VAR CurrCustomers =
  VALUES( Transactions[CustomerID] )
RETURN
  COUNTROWS( EXCEPT( PrevCustomers, CurrCustomers ) )

Step 5: Monthly churn rate measure

Calculate the rate by dividing lost customers by the total customers in the previous month. Use DIVIDE to avoid division by zero.

Taxa Churn Mensal =
VAR Perdidos = [Clientes Perdidos]
VAR PrevAtivos = [Clientes Mês Anterior]
RETURN
  DIVIDE( Perdidos, PrevAtivos )

Step 6: Best practices and common pitfalls

Main points to check:

Mark the Date table as Date table (Modeling > Mark as date table). Without this, PREVIOUSMONTH may fail.
PREVIOUSMONTH works when the time context is monthly; in a report with daily granularity adjust the logic (e.g.: DATEADD with -30 days is not the same as previous month).
COUNTROWS(EXCEPT(...)) can be expensive in models with many customers. If you detect performance issues, consider pre-calculating flags (ETL) or using aggregations.
The first month of the period has no previous month — DIVIDE ensures no division-by-zero errors appear.

Validate the result

Create a Matrix visual with 'Date'[Year] and a month field (or 'Date'[Date] formatted by month) in the rows, and the measures Clientes Mês Anterior, Clientes Perdidos and Taxa Churn Mensal in the columns. Verify:

Clientes Perdidos never exceeds Clientes Mês Anterior.
Taxa Churn Mensal = Clientes Perdidos / Clientes Mês Anterior (check a few months with manual calculation).
Null values in the first month (no previous month) should appear as blank or 0, depending on formatting.

Conclusion

With these measures you have a reliable monthly churn in DAX to monitor retention. Next steps: segment churn by product, calculate churn by cohorts or create moving averages to smooth noise. Tip: want to see churn by cohort (acquisition month) — would you like me to show that example?