How to calculate customer turnover in DAX: step by step

João Barros 30 de October de 2026 4 min read

This tutorial shows how to calculate customer turnover in DAX — that is, the percentage of customers who stopped buying between two periods — useful for monitoring customer loss and retention effectiveness. We will explain the why, use time functions and create reusable measures to obtain clear percentages by month or year.

Prerequisites

Power BI Desktop with a simple model: Sales table (CustomerID, OrderDate, Sales) and Date Table linked to OrderDate.
Basic DAX knowledge: measures, CALCULATE, FILTER, DISTINCT.
Continuous dates in the Date Table and an active relationship between Date Table and Sales.

Step 1: Understand the definition of turnover

Customer turnover is usually defined as customers lost in a period divided by the total customers in the previous period. We need to calculate: (a) customers in the previous period, (b) customers who existed in the previous period and did not purchase in the current period. Being clear avoids confusion between churn, acquisition and retention.

Step 2: Create measure for distinct customers in the period

Create a measure that counts distinct customers in Sales respecting the time context. This measure is the basis for other calculations (customers in the month, quarter, year).

Clientes Distintos = DISTINCTCOUNT(Vendas[CustomerID])

Step 3: Measure for customers in the previous period

We use SAMEPERIODLASTYEAR, PARALLELPERIOD or DATEADD depending on the period we want to compare. Here we show DATEADD for flexibility (previous month, previous year depending on context).

Clientes Período Anterior =
CALCULATE(
    [Clientes Distintos],
    DATEADD('TabelaDatas'[Date], -1, MONTH)
)

Step 4: Identify customers lost between periods

To get lost customers we need the set of customers who bought in the previous period but not in the current period. We implement a measure that uses FILTER with EXCEPT or relationship functions to compare sets of CustomerID.

Clientes Perdidos =
VAR ClientesPrev = CALCULATETABLE(
    VALUES(Vendas[CustomerID]),
    DATEADD('TabelaDatas'[Date], -1, MONTH)
)
VAR ClientesNow = CALCULATETABLE(
    VALUES(Vendas[CustomerID])
)
RETURN
COUNTROWS(EXCEPT(ClientesPrev, ClientesNow))

Step 5: Calculate turnover (percentage)

With the two numerators (Clientes Perdidos) and denominator (Clientes Período Anterior) we calculate the percentage. Use DIVIDE to avoid division by zero and format as a percentage in Power BI.

Turnover Clientes (%) =
DIVIDE(
    [Clientes Perdidos],
    [Clientes Período Anterior],
    0
)

Step 6: Handle common cases and filters

Common mistakes: (1) missing date relationships lead to incorrect values; (2) using improper context when the visual has additional filters (slicers, categories); (3) comparing periods with very low volumes produces volatile percentages. To respect product or region filters, the measures already use the current context. If you want to compare fixed periods, encapsulate DATEADD inside CALCULATE with ALL or ALLSELECTED as needed.

Clientes Período Anterior (ignorar produto) =
CALCULATE(
    [Clientes Distintos],
    DATEADD('TabelaDatas'[Date], -1, MONTH),
    ALL(Vendas[ProductID])
)

Verify the result

Create a table in Power BI with TabelaDatas[MonthYear], [Clientes Distintos], [Clientes Período Anterior], [Clientes Perdidos] and [Turnover Clientes (%)]. Confirm that:

Clientes Período Anterior corresponds to the previous month in the same context.
Clientes Perdidos + Clientes Retidos = Clientes Período Anterior (you can create Clientes Retidos = Clientes Período Anterior - Clientes Perdidos to verify).
Turnover shown as a percentage makes sense for known peaks/troughs in the data.

Conclusion

You now have measures to calculate customer turnover in DAX that you can adapt (month, quarter, year) and filter by product or region. Next steps: create visuals with lines and cards, compare with customer acquisition or segment by cohorts. Tip: test the measures in higher-volume periods to reduce noise and add a New Customers measure to see the balance between loss and acquisition.