Forum Discussion

puru85's avatar
puru85
Icon for Helper II rankHelper II
2 years ago
Solved

Help Needed: Calculating Wait Time for Outpatients in Power BI

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!


 

 

 

General Comment
Need Help
Show and Tell
Tips and Tricks
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    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!!

1 Reply

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    2 years ago

    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!!