My question is what is the best Dax formula to return the original size of a particular cohort.
To keep is simple
- I’m creating one flat table with 4 columns
The 4 columns are:
- Event date
- The amount of new signed customers in that date
- The Cohort Retention Date
- Retention Amount from the respective cohort retention date
|Event Date||New Subs||Cohort Retention Date||Retention Amount|
I first created a measure called Monthly Ending Subs to sum new subs + retention amount
and then created a table with Cohort Retention Date as the rows & the Event Date as the Columns with the Monthly Ending Subs Measure as the values.
Getting the following table
I then reused the Monthly Ending Subs measure with a filter = the previous months value, and dividing that by the Monthly Ending Subs to get the MoM % change.
What I am struggling with now is a measure to calculate the original cohort amount.
For example in row 1 (the Jan cohort) I want the measure to return 100 across the whole row for the Feb / Mar / Apr Period of Analysis.
The idea being, I can then divide the Monthly Ending Subs by this amount to get the reminding % retained form the original cohort.
I did watch the customer churn video but was unable to figure out how to transpose that information to this table layout. (also the underlying fact table is a bit different.)
Thanks for reading!