Calculate Retention Rate in given date window

Question

Hello y'all,&nbsp;I am stuck to calculate the retention rate for a given date range. Any help is highly appreciated.&nbsp;I have a list to participants who finished there 1st screening on a particular date and now they have to come again for a follow up visit in a given date range/window. If they visit in that date range then we will mark them as "Attended on time" but if they visited out of that date range then we will mark them "Out of window".&nbsp; If they don't attend at all then we mark them "Lost to follow up".This rule applies to 6 week follow up and 6 month follow up visit. See below the dataset:&nbsp;I want to calculate the retention rate of how many participant attended "on time"&nbsp; within the date range, how many attended "out of range" and how many we "lost to follow up".&nbsp;&nbsp;The major thing to consider here is: they have to have attend within the given date range.&nbsp;Record IDScrenning Completed6week windowAppointment completed6 week Status6 month window6mnth Appointment completed110/4/202110/4/2021-11/15/202110/29/2021Attended on time2/21/2022-5/16/2022&nbsp;210/14/202110/14/2021-11/25/202110/26/2021Attended on time3/3/2022-5/26/2022&nbsp;310/14/202110/14/2021-11/25/202110/26/2021Attended on time3/3/2022-5/26/2022&nbsp;410/14/202110/14/2021-11/25/202111/29/2021Out of Window3/3/2022-5/26/2022&nbsp;510/14/202110/14/2021-11/25/2021&nbsp;Lost to follow up3/3/2022-5/26/2022&nbsp;610/14/202110/14/2021-11/25/2021&nbsp;Lost to follow up3/3/2022-5/26/2022&nbsp;&nbsp;Any help is highly appreciated.&nbsp;Thanks.&nbsp;

amitchandak · Answer

axarhaan , Based on what I got 
a New column =
Switch(True() ,
isblank([Appointment completed]) , today() &gt;= [Screening Completed]&nbsp; +42, "Lost to follow up" ,
isblank([Appointment completed]) , today() &lt;= [Screening Completed]&nbsp; +42 ,"Appointment due" //You need to correct this
[Appointment completed] &gt;= [Screening Completed] +42&nbsp; , "Out of Window",
[Appointment completed] &lt;= [Screening Completed] +42&nbsp; , "Attended on Time"
)

axarhaan · Answer

amitchandak&nbsp;Thank you for your reply. I am new to power bi and still learning. I am getting an error.Can you please explain or tell me the solution for this please or how to fix it.&nbsp; Thanks.&nbsp;&nbsp;

samarth_18 · Answer

Hi&nbsp;axarhaan&nbsp;,&nbsp;Just small change on what Amit suggested. Try like below:-column =

Switch(True() ,

isblank([Appointment completed]) &amp;&amp; today() &gt;= [Screnning Completed]  +42, "Lost to follow up" ,

isblank([Appointment completed]) &amp;&amp; today() &lt;= [Screnning Completed]  +42 ,"Appointment due",

[Appointment completed] &gt;= [Screnning Completed] +42  , "Out of Window",

[Appointment completed] &lt;= [Screnning Completed] +42  , "Attended on Time"

)&nbsp;Thanks,Samarth&nbsp;

axarhaan · Answer

Samarth_18&nbsp;Hey Samarth.... Thanks for the reply.... Yes, it worked great.... Thank you for your help.... I have 1 last question, sorry for that....Can we differentiate those record ids as a group... like what if I want to show that record id 1 and 3 was in group 1 and record id 2 &amp; 4 was in group 2 and so on so forth...&nbsp; so that I can show the graphs by groups that how many attended on time in group 1 and how many lost in that group.... something like that...&nbsp;Also your solution worked to get the status of 6 week but then how to get the 6 month window... Please advice as I am new to this..&nbsp;Check below the sample dataset... Your help is greatly appreciated...&nbsp;&nbsp;GroupsRecord IDScrenning Completed6week windowAppointment completed6 week Status6 month window6mnth Appointment completedGroup 1110/4/202110/4/2021-11/15/202110/29/2021Attended on time2/21/2022-5/16/2022&nbsp;Group 2210/14/202110/14/2021-11/25/202110/26/2021Attended on time3/3/2022-5/26/2022&nbsp;Group 2310/14/202110/14/2021-11/25/202110/26/2021Attended on time3/3/2022-5/26/2022&nbsp;Group 3410/14/202110/14/2021-11/25/202111/29/2021Out of Window3/3/2022-5/26/2022&nbsp;Group 1510/14/202110/14/2021-11/25/2021&nbsp;Lost to follow up3/3/2022-5/26/2022&nbsp;Group 3610/14/202110/14/2021-11/25/2021&nbsp;Lost to follow up3/3/2022-5/26/2022&nbsp;

samarth_18 · Answer

axarhaan&nbsp;, Could you please share the condition on what basis grouping need to done?

Forum Discussion

Calculate Retention Rate in given date window

7 Replies

Related content

Retention analysis

Calculate Retention & Turnover %

Calculating 1st Year Employee Retention Rates

Employee retention data

Retention Rate

Record ID	Screnning Completed	6week window	Appointment completed	6 week Status	6 month window	6mnth Appointment completed
1	10/4/2021	10/4/2021-11/15/2021	10/29/2021	Attended on time	2/21/2022-5/16/2022
2	10/14/2021	10/14/2021-11/25/2021	10/26/2021	Attended on time	3/3/2022-5/26/2022
3	10/14/2021	10/14/2021-11/25/2021	10/26/2021	Attended on time	3/3/2022-5/26/2022
4	10/14/2021	10/14/2021-11/25/2021	11/29/2021	Out of Window	3/3/2022-5/26/2022
5	10/14/2021	10/14/2021-11/25/2021		Lost to follow up	3/3/2022-5/26/2022
6	10/14/2021	10/14/2021-11/25/2021		Lost to follow up	3/3/2022-5/26/2022

Groups	Record ID	Screnning Completed	6week window	Appointment completed	6 week Status	6 month window	6mnth Appointment completed
Group 1	1	10/4/2021	10/4/2021-11/15/2021	10/29/2021	Attended on time	2/21/2022-5/16/2022
Group 2	2	10/14/2021	10/14/2021-11/25/2021	10/26/2021	Attended on time	3/3/2022-5/26/2022
Group 2	3	10/14/2021	10/14/2021-11/25/2021	10/26/2021	Attended on time	3/3/2022-5/26/2022
Group 3	4	10/14/2021	10/14/2021-11/25/2021	11/29/2021	Out of Window	3/3/2022-5/26/2022
Group 1	5	10/14/2021	10/14/2021-11/25/2021		Lost to follow up	3/3/2022-5/26/2022
Group 3	6	10/14/2021	10/14/2021-11/25/2021		Lost to follow up	3/3/2022-5/26/2022