DAX cohort measures: preserve the acquisition group

A retention cohort holds a defined starting group fixed and asks how many of its members are active in a later period. If the denominator shrinks to only customers active in the selected month, the result can misleadingly become 100%.

Separate the cohort-defining attribute from the activity period. State whether acquisition is verified first-ever activity or merely the first activity visible in the dataset.

Import the dedicated cohort fixture

Use DimCohortCustomer, DimActivityMonth and FactCustomerMonth from the extension examples. These are separate from the eight-line retail sales scenario.

DimCohortCustomer has one row per customer with FirstObservedMonth. A and B first appear in January, C in February, D in March and E in April. FactCustomerMonth contains one row per observed active customer/month.

Relate DimCohortCustomer[CustomerID] one-to-many to the fact's CustomerID, and DimActivityMonth[MonthStart] one-to-many to its MonthStart. Use single-direction filtering from each dimension. Observation is complete for this exercise through April 2026.

FirstObservedMonth is not evidence of lifetime acquisition before the dataset begins.

Keep the denominator in the customer dimension

Cohort Size = DISTINCTCOUNT(DimCohortCustomer[CustomerID])

Active Cohort Customers = DISTINCTCOUNT(FactCustomerMonth[CustomerID])

Place DimCohortCustomer[FirstObservedMonth] on matrix rows and DimActivityMonth[MonthStart] on columns. The cohort row filters its customer members and their fact activity. The activity month filters the fact but, under the specified single-direction model, does not filter back into the cohort customer dimension.

The January denominator therefore remains two while its active count changes across months. Microsoft documents the relevant counting behaviour in its DISTINCTCOUNT reference.

Define the valid observation cells

Cohort Retention =
VAR CohortMonth = SELECTEDVALUE(DimCohortCustomer[FirstObservedMonth])
VAR ActivityMonth = SELECTEDVALUE(DimActivityMonth[MonthStart])
RETURN
    IF(
        NOT ISBLANK(CohortMonth)
            && NOT ISBLANK(ActivityMonth)
            && ActivityMonth >= CohortMonth
            && ActivityMonth <= DATE(2026, 4, 1),
        DIVIDE(COALESCE([Active Cohort Customers], 0), [Cohort Size]),
        BLANK()
    )

The fixed April cutoff is specific to this synthetic fixture. A live model should read a validated observation cutoff from its coverage metadata. The single-value checks also suppress ambiguous totals rather than presenting a combined cohort percentage without a defined interpretation.

COALESCE converts no observed activity to zero only inside the complete, eligible observation window. It must not turn future unobserved months into 0% retention.

Verify the January cohort

January's cohort is A and B. Both are active in January, A in February, B in March and A in April. Expected rates are 100%, 50%, 50% and 50%.

The same percentage in three later months does not mean the same customer remains active continuously. This is period retention, not a survival curve or uninterrupted activity measure.

February's cohort C has 100% in February, 100% in March and 0% in April under the fixture's complete observation assumption. Dates before a cohort's start are not applicable and remain blank.

Avoid denominator feedback

If you enable reverse filtering from activity facts into the customer dimension, the denominator can change with activity month. That undermines the fixed-cohort contract unless deliberately counteracted and tested.

Likewise, counting distinct customers from FactCustomerMonth for both numerator and denominator under the same month filter creates the wrong denominator. Display Cohort Size beside the rate during development to expose this error.

Keep cohort identity and activity eligibility rules explicit if product or region filters are added. “Customers acquired through Software” and “any acquired customer active in Software later” are different cohort questions.

Validate the model before adding a heatmap

Use a plain matrix of cohort size and active count first. Confirm January/April is one active customer out of two. The extension script verifies that set intersection in Python; DAX and relationship behaviour require application review.

Exercise: add May to the activity calendar without adding complete May source coverage. The May retention cells should remain unobserved, not zero. Then define a different metric for customers active in every month since acquisition and explain why the current formula does not calculate it.

NeuraPath's Data Analytics with Generative AI course connects cohort reporting with population design. A credible retention matrix preserves its starting group and distinguishes inactivity from missing observation time.

Continue learning

This article is part of the DAX measures and analytical correctness sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Calculate customer repeat rate without double-counting visits.
• Continue with Build an inventory balance from movement transactions.