topic How to do cohort analysis - same starting point with relative time period? in DAX Commands and Tips

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

ewu — Sat, 18 Mar 2023 20:32:48 GMT

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

Thanks a lot!

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

Greg_Deckler — Sat, 18 Mar 2023 21:10:36 GMT

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

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

ewu — Sun, 19 Mar 2023 04:16:24 GMT

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

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

ewu — Mon, 27 Mar 2023 23:02:25 GMT

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

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

ewu — Mon, 27 Mar 2023 23:27:59 GMT

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

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

ewu — Wed, 29 Mar 2023 00:55:19 GMT

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