Forum Discussion

Watthell234's avatar
Watthell234
Frequent Visitor
9 years ago
Solved

Cohort Analysis

Hey PowerBI world!

 

I am struggling to create a model with same behavior as the one attached to this post.

 

Essentially, I need to create a Cohort Analysis of subscriptions by using their Start Date, End Date and Amount. How can I create a Power BI model in order to create the Table?

 

Please Help! I am in deep need thanks!

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

    Watthell234

     

    In this scenario, you need to expand a list of months from StartDate to EndDate in your source. You can create a custom column like below:

     

    List.Distinct(List.Transform({Number.From([StartDate])..Number.From([EndDate])}, each Date.AddDays(Date.EndOfMonth(Date.From(_)),-Date.Day(Date.EndOfMonth(Date.From(_)))+1)))

     

     

    Then expand that Custom column:

     

     

    Then you can create a matrix like below:

     

     

    Regards,

3 Replies

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

    Watthell234

     

    In this scenario, you need to expand a list of months from StartDate to EndDate in your source. You can create a custom column like below:

     

    List.Distinct(List.Transform({Number.From([StartDate])..Number.From([EndDate])}, each Date.AddDays(Date.EndOfMonth(Date.From(_)),-Date.Day(Date.EndOfMonth(Date.From(_)))+1)))

     

     

    Then expand that Custom column:

     

     

    Then you can create a matrix like below:

     

     

    Regards,

    • Watthell234's avatar
      Watthell234
      Frequent Visitor
      9 years ago

      v-sihou-msft Now that I have this multi-axis chart, how do I create a measure that shows me the Percentage change from the baseline sum?