How to calculate cohort retention rate in DAX: step by step

João Barros 27 de August de 2026 4 min read

This tutorial shows how to calculate cohort retention rate in DAX: that is, grouping users by their first interaction (cohort) and measuring how many of those users return in the following months. Cohort analysis is useful to understand retention, onboarding effectiveness and the impact of product changes.

Prerequisites

Power BI Desktop (or another environment that supports DAX).
A events table with columns: UserID, EventDate (datetime) and EventType (optional).
Date table marked as Date and relationships between Date[Date] and Event[EventDate].

Step 1: Create the First Purchase Month (cohort) column

We need to identify the month of each user's first interaction — this will be their cohort. Let's create a calculated column in the users table or in the events table (grouped by UserID). Here we use the Event table.

FirstEventMonth =
VAR FirstDate =
    CALCULATE(
        MIN(Event[EventDate]),
        ALLEXCEPT(Event, Event[UserID])
    )
RETURN
    STARTOFMONTH(FirstDate)

Explanation: CALCULATE + MIN finds the first date per UserID; ALLEXCEPT ensures the context is only the user. STARTOFMONTH normalizes to the first day of the month, making monthly groupings easier.

Step 2: Create the MonthOfEvent column for temporal comparison

We also need a column with the month of the event itself to compare months elapsed since the cohort.

EventMonth = STARTOFMONTH(Event[EventDate])

This column is simple and normalizes each event to the start of its corresponding month.

Step 3: Calculate months since cohort

Now we calculate how many months separate EventMonth and FirstEventMonth to pivot retention by month (M0, M1, M2...).

MonthsSinceCohort =
DATEDIFF(Event[FirstEventMonth], Event[EventMonth], MONTH)

DATEDIFF in MONTH returns 0 for the cohort month, 1 for the following month, etc.

Step 4: Create the unique users measure by cohort and month

We want to count how many unique users, by combination of FirstEventMonth and MonthsSinceCohort, had at least one event. We create a measure for this.

Users by Cohort =
CALCULATE(
    DISTINCTCOUNT(Event[UserID]),
    VALUES(Event[FirstEventMonth]),
    VALUES(Event[MonthsSinceCohort])
)

VALUES ensures the count respects the filter of the cohort and months columns in the visual (table or matrix).

Step 5: Create the cohort size measure

To get the retention rate we divide by the number of users in the cohort in month zero (M0). We create a measure that calculates the cohort size (users in M0) for each FirstEventMonth.

Cohort Size =
VAR CohortMonth = SELECTEDVALUE(Event[FirstEventMonth])
RETURN
CALCULATE(
    DISTINCTCOUNT(Event[UserID]),
    Event[FirstEventMonth] = CohortMonth,
    Event[MonthsSinceCohort] = 0
)

SELECTEDVALUE gets the cohort from the current context. The filter MonthsSinceCohort = 0 isolates only the users in the initial month.

Step 6: Retention rate measure

Finally, we calculate the percentage that returned in month N by dividing Users by Cohort by Cohort Size. We use DIVIDE to avoid division by zero.

Retention Rate =
DIVIDE(
    [Users by Cohort],
    [Cohort Size],
    0
)

This measure returns values between 0 and 1; multiply by 100 in the visual if you want a percentage.

Verify the result

Create a Matrix in Power BI with rows as Event[FirstEventMonth] (cohort), columns Event[MonthsSinceCohort] (ordered 0,1,2...) and values as [Retention Rate] (format as percentage) and [Cohort Size] for reference. Check that:

In the MonthsSinceCohort = 0 column, Retention Rate is 100% (or 1) because cohort size divides by itself.
Numbers decrease gradually for later months, except justified spikes.
If you see unexpected differences, verify that FirstEventMonth and EventMonth columns are correct and that the date table is related correctly.

Conclusion

With these measures you have a foundation for cohort analyses in DAX: identify cohorts, measure month-by-month retention and compare cohorts over time. Next steps include adjusting for weekly retention, segmenting by channel or EventType, and creating heatmaps for visualization. Tip: use date filters and slicers to validate scenarios and always confirm that the first event is correctly calculated — that error is very common.