Help! Calculating Wait time

[puru85]

Hi Experts,

Attached is my PBIX with Datamodel. 
Hospital_Outpatients_Waittime.pbix

I am finding it challenging to achieve the correct results for wait time based on the following logic:

Wait Time Calculation Logic:

Wait Time =
IF (
'Invoice_statushis'[Created_on_Time] (Status = 'Checked In') < 'Appointment'[Start_time],
'Invoice_statushis'[Created_on_Time](Status = 'Consult Start') - Appointment.AppointmentTime,
'Invoice_statushis'[Created_on_Time](Status = 'Consult Start') - Invoice_statushis'[Created_on_Time](Status = 'Checked In')
)

Anyone, Please Help!!

[puru85]

@sevenhills 
Thank you for your great help!! Much appreciated. 

[sevenhills]

Glad to hear it helped!

[sevenhills · Accepted solution]

Try this and see 

Add this column to your appointment table!

Column Wait Times = 

-- Get the invoice id of the same patient id and same date
var _inv_pid_appt2 = CALCULATE( MAX(Invoice[_id]), FILTER( ALLSELECTED(Invoice), Invoice[patient_id] =  appointment[patient_id] && Invoice[date].[Date] = appointment[Start_time].[Date]) )

-- Get the checked in record time of the same invoice id
var _inv_statushis_pid_appt2_checkedIn = CALCULATE( MAX(Invoice_statushis[Created_on_Time]), FILTER( ALL(Invoice_statushis), Invoice_statushis[invoice_id] = _inv_pid_appt2 && Invoice_statushis[Status] = "Checked In") )

-- Get the consult start record time of the same invoice id
var _inv_statushis_pid_appt2_consultin =  CALCULATE( MAX(Invoice_statushis[Created_on_Time]), FILTER( ALL(Invoice_statushis), Invoice_statushis[invoice_id] = _inv_pid_appt2 && Invoice_statushis[Status] = "Consult Start") )

RETURN 
IF (
    _inv_statushis_pid_appt2_checkedIn < appointment[Start_time],
    DATEDIFF(_inv_statushis_pid_appt2_consultin, appointment[Start_time],  MINUTE),
    DATEDIFF( _inv_statushis_pid_appt2_consultin, _inv_statushis_pid_appt2_checkedIn, MINUTE)
)

First, I tried getting the invoice id for the same patient id and patient appointment date.

Second, I used the invoice id and then filter the checked in and consult start events and get the max time 

Third and last step, tried to do your logic calculation.  You may have to tune further this step as it does not make sense purely if statement vs true condition calc. My thinking says as 

_inv_statushis_pid_appt2_consultin < appointment[Start_time]

Tune to your needs and see if this helps!