Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
tomislav_mi
Helper II
Helper II

Dynamic Agregation - Cohort table

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 MRRMonthsActive    
Row Labels12345
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 Labels12345
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

 12345
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!

3 REPLIES 3
AlexisOlson
Super User
Super User

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

@AlexisOlson 

Thank you for your response!

Unfortunately, it doesn't.

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.