Help Needed: Calculating Wait Time for Outpatients in Power BI

[puru85]

Hi Experts,

I'm working on calculating the wait time for outpatients in Power BI.

My dataset includes two tables:
1) Appointment: Contains details about scheduled appointments, including the appointment time.
2) OutpatientVisit: Captures information on outpatient visits, including timestamps for 'Checked In' and 'Consult Start' statuses.

Requirement:

I need to calculate the wait time for outpatients based on the following logic:

Wait Time =
IF (
OutpatientVisit.VisitTime (VisitStatus = 'Checked In') <  Appointment.AppointmentTime,
OutpatientVisit.VisitTime (VisitStatus = 'Consult Start') - Appointment.AppointmentTime,
OutpatientVisit.VisitTime (VisitStatus = 'Consult Start') - OutpatientVisit.VisitTime (VisitStatus = 'Checked In')
)

Can anyone advise on the best way to achieve this calculation in Power BI using using DAX. I am fine with either calulated column or Measure. 

Attached is the dataset and PBIX. 

WaitingTime.xlsx
Waittime.pbix


Thank you!

[rajendraongole1 · Accepted solution]

Hi @puru85 - you can achieve this with a similar logic using a measures.

Adjust the DATEDIFF function to use MINUTE, SECOND, or another unit of time as needed.

for waittime measure:

WaitTimeMeasure =
VAR AppointmentTime =
MAXX (
RELATEDTABLE(Appointment),
Appointment[AppointmentTime]
)

VAR CheckedInTime =
CALCULATE (
MAX (OutpatientVisit[VisitTime]),
OutpatientVisit[VisitStatus] = "Checked In"
)

VAR ConsultStartTime =
CALCULATE (
MAX (OutpatientVisit[VisitTime]),
OutpatientVisit[VisitStatus] = "Consult Start"
)

RETURN
IF (
CheckedInTime < AppointmentTime,
DATEDIFF(AppointmentTime, ConsultStartTime, MINUTE),
DATEDIFF(CheckedInTime, ConsultStartTime, MINUTE)
)

Hope it should works in calculate the wait time for outpatients. 

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!