Dax for Original Cohort Amount


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:

  1. Event date
  2. The amount of new signed customers in that date
  3. The Cohort Retention Date
  4. 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!

So I created a very detailed workshop on this particular topic.

I want to make sure you’ve gone through this in depth because it covers a lot of what you need to do to get cohort analysis working well in Power BI.

Please see below

Lots of it has to do with getting the model set up correctly. I cover this throughout the workshop.

See how you go reviewing this