Forum Discussion
Cohort Analysis
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-trend/ba-p/882970
I have discussed similar stuff here
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/296882f3d11e581c41f30b40bedb7ca520210412145237/3a172a) .
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!