Hello,
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 |
|---|---|---|---|
| 1/1/2019 | 100 | 1/1/2019 | - |
| 2/1/2019 | - | 1/1/2019 | 90 |
| 2/1/2019 | 150 | 2/1/2019 | - |
| 3/1/2019 | - | 1/1/2019 | 81 |
| 3/1/2019 | - | 2/1/2019 | 135 |
| 3/1/2019 | 160 | 3/1/2019 | - |
| 4/1/2019 | - | 1/1/2019 | 73 |
| 4/1/2019 | - | 2/1/2019 | 122 |
| 4/1/2019 | - | 3/1/2019 | 144 |
| 4/1/2019 | 170 | 4/1/2019 | - |
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!