Cohort Analysis - DAX

Hi Steve,

Here are a couple of ideas I can think of depending on the logic you want to apply (they give the same answer using your sample data).

These are additive over signup month (which you may want) and invoice month (which you may not want). You can modify that behaviour if needed.

1. Denominator = For each Signup Month, Number of customers where Invoice Month = Signup Month

=
DIVIDE (
    [Count of Customer ID],
    CALCULATE (
        [Count of Customer ID],
        GENERATE (
            VALUES ( Table1[Signup Month] ),
            FILTER (
                ALL ( Table1[Invoice_Month] ),
                Table1[Invoice_Month] = Table1[Signup Month]
            )
        )
    )
)

2. Denominator = For each Signup Month, Number of customers in first Invoice Month with nonblank customers

=
DIVIDE (
    [Count of Customer ID],
    CALCULATE (
        [Count of Customer ID],
        GENERATE (
            VALUES ( Table1[Signup Month] ),
            FIRSTNONBLANK ( ALL ( Table1[Invoice_Month] ), [Count of Customer ID] )
        )
    )
)

Owen :)