Tracking Customer Retention with Cohort Analysis (M0, M1, M2…)

Cohort analysis groups customers by their first purchase month and tracks how many return in M0, M1, M2… (months since that first purchase). You build calculated columns on Orders, then a matrix visual for retention.

FirstPurchaseDate =
CALCULATE(
    MIN ( Orders[OrderDate] ),
    ALLEXCEPT ( Orders, Orders[CustomerID] )
)

CohortMonth =
EOMONTH ( Orders[FirstPurchaseDate], 0 )

MonthDistance =
DATEDIFF (
    Orders[CohortMonth],
    EOMONTH ( Orders[OrderDate], 0 ),
    MONTH
)

CohortPeriod =
"M" & Orders[MonthDistance]

• FirstPurchaseDate — anchor date per customer (ignores slicers except customer).
• CohortMonth — end of month of first purchase; all customers acquired in Jan 2024 share one cohort.
• MonthDistance — 0 in acquisition month, 1 next month, etc.
• CohortPeriod — label M0, M1, M2 for matrix columns.

1. Insert a Matrix visual.
2. Rows: CohortMonth (format as YYYY-MM or month name).
3. Columns: CohortPeriod (sort by MonthDistance, not alphabetically).
4. Values: Distinct Count of CustomerID (or a measure counting active customers in period).
5. Optional: % of M0 measure = divide each cell by cohort size at M0 for retention rate.
6. Slicer: Product Category or Region to compare cohorts side by side.