Dynamic Agregation - Cohort table

[tomislav_mi]

Hey guys,

Hope you are keeping well!

Can you please help me on this topic:

Made a cohort table (as pivot table from the data set) that looks like this 

Sum of MRR	MonthsActive
Row Labels	1	2	3	4	5
2020-02	18,960	18,960	9,480	9,480	9,480
2020-03	18,480	14,460	18,480	10,440	18,480
2020-04	7,200	7,200	7,200	7,200	7,200
2020-06	13,200	13,200	13,200	13,200	13,200
2020-07	9,480	9,480
2020-09	15,384	17,064	17,064	17,064	17,064
2020-10	8,040	8,040	8,040	8,040	8,040
2020-11	6,834	6,834	6,834	6,834	6,834
2020-12	24,440	24,440	24,440	24,440	24,440
2021-03	37,100	35,948	35,948	35,948	35,948
2021-05	20,876	20,876	27,968	27,968
2021-06	10,610	10,610	10,610

and made a measure that is calculating retention per month active that works like this

Retention%=
DIVIDE (
    SUM ( [MRR] ),
    CALCULATE ( SUM ( [MRR] ), [MonthsActive] = 1, VALUES ( [Cohort] ) )
)

that gives me this result

	MonthsActive
Row Labels	1	2	3	4	5
Retention%	100%	98.17%	94.05%	94.19%	94.02%

but the result is somehow wrong: months 1,2,3 are calculated good, but 4 and 5 are wrong

The results should be

	1	2	3	4	5
Retention%	100.00%	98.17%	94.05%	89.23%	88.42%

and the reason is that formula I have, excludes cohort 2020-07 from the beginning sum.

Hope there is some possible improvement in the formula so it can calculate it correctly.

Please help.

Many thanks!

[AlexisOlson]

Does adding ALL ( DimDate[Year-Month] ), assuming that's the appropriate column name, as another argument in CALCULATE help?

[tomislav_mi]

@AlexisOlson 

Thank you for your response!

Unfortunately, it doesn't.

[AlexisOlson]

Are your months coming from a date dimension table or the same table as [MMR]?

If you don't have a separate dimension table, you might be running into an Auto-Exist issue.