cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anwilkins
Frequent Visitor

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

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])

anwilkins_0-1630495168085.png

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

 

1 ACCEPTED SOLUTION
yingyinr
Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
yingyinr
Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Users online (283)