How to do cohort analysis - same starting point with relative time period?

[ewu]

Could someone please help me? Left table is what I have, and the right 2 tables are what I need.

Thanks a lot!

[Greg_Deckler]

@ewu Try this: Patient Cohort (AND Slicer) - Microsoft Power BI Community or this: Customer Retention Part 3: Period Of Stay – Cohort... - Microsoft Power BI Community

[ewu]

Thanks Greg. The 2nd link is quite similar to what I wanted. Let me try to do it the same way first these a few days. @Greg_Deckler 

[ewu · Accepted solution]

Hi, Greg,

The 2nd link above was helpful but still not enough for me. The challenges are:

1. Customer starting dates are in another table which is properly linked to the left table in my original screenshot above. That is, the left table in the screenshot only starts from a recent month while many more historical sales data are to be input.  I tried "related" with "datediff", but couldn't get the age.

2. Even if I do get the customer age, how do I pupulate a table that looks like those in the right table in my screenshot above? The columns headers should read "1st month from onboarding, 2nd month from onboarding,.... 50th month from onboarding". 

Could you please help? Thanks a lot. @Greg_Deckler 

[ewu]

No worries. My mistake. Anything minor could kill everything. 

[ewu]

Unfortuantely I went through the above videos and a couple more, but still cannot get the final results. One of the challenges is I am not sure what to do with my newly created generateseries table. Here are the 2 tables I have now:

Below is what I want:

Could someone please help? @Greg_Deckler