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 S...
  • 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,

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

    Amazing response time v-sihou-msft! Worked like a charm. 

     

    Thank you so much for the help!

     

     

     

     

  • 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?