Cohort Analysis

[samdep]

Hi Everyone!

I am building a Cohort Analysis and am, I think, most of the way there -- but am struggling with my DAX Retention expression.

I have a case open date (cases are grouped into cohorts by the month in which they are onboarded) -- that conditional column is working as expected. I then generated a series 0-12 to represent their signup month (0) and I am now attempting to visualize rates of retention across a 12 month period.

For some reason that I cannot quite figure out, Month 0 is not showing 100% -- which in another CA I built out last week where there is a defined start and end date, as well as activity dates in between, all seems to be working fine as Month 0 is showing as 100%, then Month 1 at 80%.... etc... 

For cases that are still open (ie: no value in the closed date field, I am using today/current month) -- Not much is different between these analyses, so I'm not sure where I'm going wrong. DAX below, along with actual v. expected results. Greatly appreciate all help!

Client Retention (Case Open to Case Close/Today If Not Yet Closed) % =

VAR FollowingMonths = SELECTEDVALUE('Months After'[Value])   // Series 0-12 generated in a new table - working as expected.

VAR CaseOpenDate = SELECTEDVALUE('Case'[Case Open Date (Cohort Month)])  // Cohort Grouping -- Month of Onboarding - Conditional Column

RETURN

DIVIDE(

CALCULATE(

COUNT('Case'[CaseNumber]),   // 

FILTER(

'Case',

EOMONTH('Case'[ClosedDate],0) = EOMONTH(CaseOpenDate, FollowingMonths)

)

),

DISTINCTCOUNT('Case'[CaseNumber]))

Here's what I'm getting...

Cohort   Month 0   Month 1  Month 2

Jan 21   30%       22%        18%

Feb 21  50%        40%        12%

Mar 21

Etc.

Here's what I'm hoping to get... 

Cohort   Month 0   Month 1  Month 2

Jan 21   100%       30%        18%

Feb 21   100%       50%        12%

Mar 21   100%       Etc.

Etc.

[amitchandak]

@samdep , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Actually how can be case closed by 100% in month 1

DISTINCTCOUNT('Case'[CaseNumber]) is the open case in that month?

Other closed in that Month?

Are looking for opened vs open ratio?

refer if this can help https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

I have discussed similar stuff here

https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-3-Period-Of-Stay-Cohort-Anal...

[samdep]

Hi Amit,

Thanks for your response - I'll read through your recommended posts, thank you!

I can't upload a PBIX for some reason here, but I have a link to download a file of sample data (https://wetransfer.com/downloads/fd3ebcb142a797a5b578a34d4dd3adf720210412145224/296882f3d11e581c41f3...) .

The first tab (Task Activity) I'm currently using as a proxy for case open/close. In that table, all activity with a client is captured in a single field from first activity date to last and I was able to group cases/clients into cohorts based on first activity date and show retention over a 12 month period based on task activity dates associated with that client through the next 12 months.

The trouble I am having with the case data is that - just like task activity - there is a distinct open date (first activity), but there may not yet be a case close date -- so, I created a conditional column that populates with the case close date if there is one, otherwise today's date.

The expression that worked in the task activity retention analysis doesn't function properly with this table of data - I think I need to add an aging field and tie that to the Months After field to properly show retention across the months between case open date and close date.

As far as 100% in Month 0 -- I'm thinking of Month 0 as the month the client was onboarded, so Month 0 would be 100% across the months. Then, Month 1, we'll start to see that % drop based on how many cases were closed.

Basically, I'm looking to group cases by the month/year in which they were opened, then show a retention % for the following 12 months - but I'm thinking now the expression isnt working properly because there isnt necessarily an activity date associated with a client in the next month, it's one date for open/one for close - so, I guess I'm probably missing that aging field. Thank you again!