[SOLVED] How could I fix this DAX code to get the customer churn matrix I would like in Power BI?

How could I fix this DAX code to get the customer churn matrix I would like in Power BI?

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 ID])),
        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.

Here is a stripped version (due to data privacy) of the pbix file. https://drive.google.com/file/d/1sB9Btvl2Fx1P26cfyROtZAilkzln_Q6i/view?usp=drive_link

Could someone help me, please?

Solution

Try this one (which doesn't need any calculated columns on your table).

Attempt3 = 
  var sDate = EOMONTH(MAX('Calendar_table'[Dates]), 0)
  var eDate = EOMONTH(sDate, SELECTEDVALUE('Months After'[Value]))
  return 
    CALCULATE(
      DISTINCTCOUNT('Subscriptions_export'[Customer ID]),
      REMOVEFILTERS('Calendar_table'),
      EOMONTH('Subscriptions_export'[Created at], 0) = sDate &&
      (
        EOMONTH('Subscriptions_export'[Cancellation date], 0) >= eDate || 
        ISBLANK('Subscriptions_export'[Cancellation date])
      )
    )