I want to create a Cohort Analysis. This involves calculating the the percentage of a cohort that is left in each of the subsequent months following an initial signup. The screenshot below demonstrates the basic concept. I want to avoild hard coding any date filters as a new cohort is added each month.
I am thinking the solution involves a CALCULATE formula, but I am getting tripped up with the DAX to identify the denominator in the % calculation (the starting number of customers in each Cohort (40 in the first cohort, 35 in the second 30 in the 3rd cohort).
Can anyone help me out with the DAX?
Any help is much appreciated. Excel/ PowerPivot File attached.