Time from hospital discharge to first follow up appointment

[jpvernon67]

Relatively new to DAX and I am struggling to get my head around the solution to this problem. I have searched for a solution but nothing seems to fit,

I have 3 tables:

Patient provide this has the unique client ID in it

Hospital discharge table has the unique client ID, discharge date. Has many values for the same client ID - I have no control over this table. Comes from the hospital.

Schedule data also has the client ID, appointment dates, appointment types. This also has many fields.

There is no direct link other than client ID between these 2 tables. i.e there is no 'event ID' that would connect it to the associated follow up appointment.

What I need to get is the number of days from the patient's discharge date to the first outpatient appointment date (can't be the same day)

The results would look something like this:

Client ID	Discharge Date	Follow Up Date	Days
112345	1/1/2022	1/5/2022	4
43235	4/1/2022	4/7/2022	6
532623	3/12/2022	3/14/2022	2
64452	2/14/2022	2/18/2022	4
426778	3/11/2022	3/20/2022	9
		Median Days	4

What I need for the report is the row total days and the aggregate median days per period FY, FYQ,...

Any insight would be very appreciated

[jpvernon67]

Sorry about that

here is a link to the files:

https://1drv.ms/u/s!AptRNMy-TRKq4wvd6mrjQTUyIacP?e=y2mWAY

Here is what the output should look like:

PatientID	Event	Discharge Date	Follow Up Date	Days
54918	Emergency Discharge	4/17/2021	4/28/2021	11
55069	Emergency Discharge	4/17/2021	4/26/2021	9
55079	Emergency Discharge	4/17/2021	4/22/2021	5
40509	Emergency Discharge	4/19/2021	4/29/2021	10
42195	Emergency Discharge	4/19/2021	4/28/2021	9
15283	Inpatient Discharge	01/31/2022

it is also possible that the person did not have a follow up appointment like patient 15283. I should be able to get the median days to follow up by both patient and as an aggregate by time period.

[Shishir22]

Hello @jpvernon67 ,

Request you to please provide sample data of all the tables and expected result so that we can help you.

Thanks,

Shishir

[jpvernon67]

here is a link to the files:

https://1drv.ms/u/s!AptRNMy-TRKq4wvd6mrjQTUyIacP?e=y2mWAY

Here is what the output should look like:

PatientID	Event	Discharge Date	Follow Up Date	Days
54918	Emergency Discharge	4/17/2021	4/28/2021	11
55069	Emergency Discharge	4/17/2021	4/26/2021	9
55079	Emergency Discharge	4/17/2021	4/22/2021	5
40509	Emergency Discharge	4/19/2021	4/29/2021	10
42195	Emergency Discharge	4/19/2021	4/28/2021	9
15283	Inpatient Discharge	01/31/2022

it is also possible that the person did not have a follow up appointment like patient 15283. I should be able to get the median days to follow up by both patient and as an aggregate by time period.