Calculating drop off clients

[datahustle]

I have a table that has client data in it and i'm trying to calculate retention for each month. The problem is this: 

i have someone named Sarah. her first visit was on 2/28/2024. That was also her last visit. in order to identify when Sarah 'dropped off' as a client, I add 45 days to the last session. So 2/28 + 45 days would be 4/13/2024. 

I have 2 date fields. one that shows each session date that a client arrives for. the other is the dropoff date. So if i want to look at February stats (by filtering on session date for february), I would see that Sarah is a new client, because her first session was in February and she hasn't yet dropped off until that 45 day threshold kicks in.  But when I set the session date filter to April, because she only has 1 record in february, and never returned, she doesn't show up as a drop-off.  the 4/13 date is in the dropoff date field, not session date.

One idea i had was to add an artificial row for each client who's dropped off under session date that would act as their dropoff date but i don't think that's the best way to set it up. 

Any ideas? Attached is an example of what i'm seeing: 

This is the current measure I'm using: 

Dropped_Off_Clients =

CALCULATE(

    DISTINCTCOUNT('Sheet1'[patient_number]),

    'Sheet1'[Is_Dropped_Off] = 1

)

[datahustle]

here's some sample data:

In terms of expected output, i would want to see drop-offs by month (month being departure date)

[Ashish_Mathur]

I cannot do much with an image.  Share data in a format that can be pasted in an MS Excel file.

[datahustle]

i keep trying to copy/paste the sheet in here but i get the following error: 

Your post has been changed because invalid HTML was found in the message body. The invalid HTML has been removed. Please review the message and submit the message when you are satisfied.

[datahustle]

treatment_type

patient_number

patient_name

staff_member_name

state

first_visit

drop_off_flag

first_session_date

session_date

last_session_date

last_session_datedropoff_threshold

Month (number)

Month (name)

Day of month

Week

Year

Individual Therapy

162

John Jackson

Miranda

arrived

TRUE

1

12/27/2023

12/27/2023

12/27/2023

2/10/2024

12

December

27

52

2023

Individual Therapy

129

Yao Ming

Ivana

arrived

TRUE

1

12/2/2023

12/2/2023

12/2/2023

1/16/2024

12

December

2

2023

Individual Therapy

131

Becca Johnson

Moe

arrived

TRUE

1

12/5/2023

12/5/2023

12/5/2023

1/19/2024

12

December

5

2023

Individual Therapy

138

Logan Wilson

Moe

arrived

TRUE

1

12/5/2023

12/5/2023

12/5/2023

1/19/2024

12

December

5

2023

Individual Therapy

151

Robert Pohl

Tatyana

arrived

TRUE

1

12/15/2023

12/15/2023

12/15/2023

1/29/2024

12

December

15

2023

Individual Therapy

146

Shahab Yaghi

Miranda

arrived

TRUE

1

12/22/2023

12/22/2023

12/22/2023

2/5/2024

12

December

22

2023

Individual Therapy

196

Joe Rogan

Tatyana

arrived

TRUE

1

1/29/2024

1/29/2024

1/29/2024

3/14/2024

1

January

29

2024

[Ashish_Mathur · Accepted solution]

Hi,

Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name by the Month number.  Create a relationship (Many to One and Single) from "last_session_datedropoff_threshold" column to the Date column of the Calendar Table.  To your visual, drag the Year and Month name column from the Calendar Table.  Write this measure

Dropped off clients = DISTINCTCOUNT('Sheet1'[patient_number])

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.