cancel
Showing results for
Did you mean:
Frequent Visitor

## How to compare dates from two related tables and generate accuracy rate

Hello. I work in a hospital and we want to compare the actual discharge date of a patient with accuracy of our estimate from earlier. I have two tables related (1:many) by a patient ID number (MRN).

Table 1 - Yesterdays DCs

• MRN
• Disch Date

Table 2 - DC Info - This table give patient information on a daily basis. There are multiple/duplicate MRN in this table and I am interested in Expected Dischrg when Report Date/Time is latest.

• MRN
• Expected Dischrg
• Report Date/Time

Can you help me with a measure that will give the percentage of MRNs that have the correct Expected Dischrg date?

Example:

Below, you can see, on 3/24, we discharged 9 patients where we had predicted they would discharge on 3/24. And there where 20 patients discharged where we had the estimate wrong.

Side note - Not to sidetrack, but a second question...I think the column above is misleading or wrong, "Latest Expected Dischrg" is not the same as "last reported" Expected Dischrg which would be more accturate.

Thank you!

3 REPLIES 3
Super User

@MattRo , You need have common MRN dimension, post that you need have measures

measure =

var _diff = datediff(max(Table1[DesChg]), max(Table2[Expected DesChg]), day)

return

countx(values(MRN[MRN]), if(_diff  <>0 , [MRN], blank() ) )

Now you can divide ot with countrows(filter(Table, Table1[DesChg] <> Blank()))

Frequent Visitor

@amitchandak
Using your measure, I managed to come up with this which identifies the matches. I think I am on the right track, but I am not able to sum. Any ideas?

using this measure:

EDD Accurate? =
var _diff = datediff(max('Yesterday DCs'[Disch Date]), max('DC info'[Expected Dischrg]), day)
return
countx(
values('Yesterday DCs'[MRN.DCd]),
if(_diff  <>0 , blank(), 1)
)
Frequent Visitor

@amitchandak  Hi. Thanks for the response! Could you elaborate on "You need to have a common MRN dimension"? Do you mean I need to create a separate table containing MRN only? What would the purpose be given that I already have a table with a column of all unique MRN values? Thank you.

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors