Import date column from one table into another table so I can count days between

[anwilkins]

Working with medical data from two data sources. We offer both drug and alcohol services as well as other counseling, physical health services, etc... I am seeking the average number of days a patient remained in other programs after being discharged from say a substance use program. 

I pull the discharge date from the substance use program from ('RDT'[Discharge Date]) 

I pull the last time the patient was seen at any other office for any other reason (i.e. free med program, weight managmentment program, family counseling, etc) from ('PD'[LastServiceDate]  

I have the two tables linked by Patient ID #

I need help to either:

• Copy the RDT-Discharge Date into the PD table then I can count the days between or...
• Create a measure to count the days between the above dates.

I do have a filter in place to only display patients with a Discharge Date.

Also note I have tried the below DAX for a new column and it hosed up the dates. (see the red lines)

Imported RDT Date = SUMX(RELATEDTABLE('RESET Diagnostics Tracker'), 'RESET Diagnostics Tracker'[Discharge Date])

The end goal is to have 2 displays.

First display is to add a column to the above matrix so it looks like this:

RDD                    LSD                Days between dates

12/17/2019         1/17/2020             30

1/27/2020         7/27/2021               547

and a second display that's just a data card with the Average days for all patients.

Thanks

[v-yiruan-msft · Accepted solution]

Hi @anwilkins ,

First, you can create a calculated column in PD table to get the Discharge Date from RDT table:

Imported RDT Date =
CALCULATE (
    MAX ( 'RESET Diagnostics Tracker'[Discharge Date] ),
    FILTER (
        'RESET Diagnostics Tracker',
        'RESET Diagnostics Tracker'[Patient ID #] = EARLIER ( 'PD'[Patient ID #] )
    )
)

Then create a measure as below to get the days between Discharge Date of RDT table and Last Service Data of PD table:

Days between dates =
DATEDIFF (
    SELECTEDVALUE ( 'PD'[Imported RDT Date] ),
    SELECTEDVALUE ( 'PD'[LastServiceDate] ),
    DAY
)

In addition, you can refer the content in the following links to get it.

CALCULATE DAYS BETWEEN TWO DATES / FIELDS IN DIFFERENT TABLES

If the above ones can't help you get the expected result, please provide some sample data with Text format(exclude sensitive data) and your expected result with specific examples and backend logic. Thank you.

Best Regards