Cohort analysis - customer churn matrix

[Eszti]

Hi everyone,
I am working on a project that I am not qualified for and despite doing a lot of research and trying to follow tutorials I am still stuck with my cohort analysis.

I am trying to create a matrix showing the development in the number of active customers in each cohort over time, as it is shown in the article below.

https://medium.com/@ramzaftab/learn-how-to-build-cohort-analysis-using-power-bi-b13a38e80e9d

For rows, I have Calendar_table[Month & Year]. The calendar table has a one-to-many relationship to the subscription table.

For columns, I am trying to use
Months After = GENERATESERIES(0,11,1)

Attempt1 =
VAR CohortStartMonth =

    SELECTEDVALUE ( Subscriptions_export[Join Month] )

VAR CurrentMonth =

    EOMONTH ( CohortStartMonth, SELECTEDVALUE ('Months After'[Value]))

RETURN

    CALCULATE (

        COUNTROWS ( VALUES(Subscriptions_export[Customer email])),

        Subscriptions_export[CreatedatAtDate EOM] = CurrentMonth)

Attempt2=

CALCULATE(

    VAR minDate = MIN(Subscriptions_export[Join Month])

    VAR maxDate = CALCULATE(MAX(Subscriptions_export[Exit Month]), NOT(ISBLANK(Subscriptions_export[Exit Month])))

    RETURN

    COUNTROWS(

        FILTER(

            Subscriptions_export,

            Subscriptions_export[Join Month] >= minDate

            && Subscriptions_export[Exit Month] <= maxDate

        )

    )

)

When I try to use the measures to create the matrix attempt 1 only shows one column for time 0 and attempt2  displays the same number (the value for time 0) for all ‘months after’ in every row.

Could anyone help me, please?

[Eszti]

Sorry for the very late reply. Here is a stripped version (due to data privacy) of the pbix file. The only change I made relative to the original post is that I am using 'Customer ID' as the primary key instead of 'Customer email'.

https://drive.google.com/file/d/1sB9Btvl2Fx1P26cfyROtZAilkzln_Q6i/view?usp=sharing 

[Anonymous]

Hi @Eszti ,

Can you provide the pbix file, please?

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.