Cohort Analysis

[Watthell234]

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 · Accepted solution]

@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]

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

[Watthell234]

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

Thank you so much for the help!