I have a dataset showing club members that includes a start date and end date. If they are current members, the end date is null.
Dataset looks like this:
Club | MemberID | StartDate | EndDate
Pinegrove | 123 | 1/1/22 | 7/1/22
Webster | 456 | 3/18/20 | 6/3/22
I want to create a report showing member attrition by club. I'd like the X-axis to be number of days, and Y-axis to be percentage of members still active on that day, color-coded by Club that they belong to.
So the goal is something like this:
I created a column called Tenure that's calculated as IF ISNULL([EndDate]) THEN MaxTenure ELSE [EndDate] - [StartDate] END. MaxTenure is a the maximum length that anyone has been active - I need this field so that active individuals will show up in my graph. I also have a count of total members for each club.
So what I'm looking to do is create a field that calculates TotalMembers - (TotalMembers with Tenure < X), and then divides by TotalMembers. X is the number of days on the X-axis. On day 0, this value should be 100% for all clubs.
I apologize if this is unclear - any assistance is appreciated.
Thanks!