cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Helper II

## Dynamic Agregation - Cohort table

Hey guys,

Hope you are keeping well!

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] = 1VALUES ( [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.

Many thanks!

3 REPLIES 3
Super User

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

Helper II

@AlexisOlson

Unfortunately, it doesn't.

Super User

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.