Forum Discussion

Watthell234's avatar
Watthell234
Frequent Visitor
9 years ago
Solved

Cohort Analysis

Hello All,

 

I am trying to perform a Cohort Analysis.

 

 

My Dataset looks as follows: 

 

 

 

 

 

 

 

 

Desired Output is the percentage remaining over time:

 

 

 

 

 

 

How to create a dynamic Measure that is able to change no matter what the cohort is. Meaning if I were to switch the start date for state or anything like it, I am able to see the decay over counter.

 

Please help! stuck for weeks.

Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    Watthell234

     

    In this scenario, I don't know what your MRR mean and how your percentage remaining calculate.

     

    But basically, you need to have to full calendar table and build relationship between monthly counter and date column. Then you can create a matrix, put StartDate as row group and Date on column group. If you want to use current MRR divide by total MRR under same StartDate. You can create a measure below and put it in Value.

     

    =
SUM ( Table[MRR] )
    / CALCULATE (
        SUM ( Table[MRR] ),
        ALLEXCEPT ( Table, Table[StartDate], Table[UniqueId] )
    )

    Regards,

     

2 Replies

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    9 years ago

    Watthell234

     

    In this scenario, I don't know what your MRR mean and how your percentage remaining calculate.

     

    But basically, you need to have to full calendar table and build relationship between monthly counter and date column. Then you can create a matrix, put StartDate as row group and Date on column group. If you want to use current MRR divide by total MRR under same StartDate. You can create a measure below and put it in Value.

     

    =
SUM ( Table[MRR] )
    / CALCULATE (
        SUM ( Table[MRR] ),
        ALLEXCEPT ( Table, Table[StartDate], Table[UniqueId] )
    )

    Regards,