How to Calculate Time on Waitlist in Power BI

Tristan Keelan
Jan 31, 2021 10:04:20 PM


HubSpot Video


This video tutorial outlines how to use Power BI to calculate time on a waitlist for those current awaiting services.  It is important to be keeping an eye on this metric as you strive for as short a time spent waiting as possible.  The quicker clients get engaged in service after seeking it out, the more likely their treatment is to get off to a productive start.

Once you have calculated the time on waitlist you may want to consider looking at how many are on the waitlist, or who is past a certain goal point for follow-up, or if there are variants by program or location.  While many measures take a look at the past, your current time on waitlist is the type of report you want to look at regularly so you can take action.  If you aren't sure what a good goal is, start by looking at your historical time between referral and admit times to get your current baseline for goal setting.

You can use the formula for Length of Stay with Referral Date and Admit date to find that number, and read about the importance of time to service in 3 Outcomes Categories to Target for PDSA.

After calculating waitlist time for each case and exploring averages, it may be beneficial to calculate the percentage of cases that are over/under your desired metric to help create urgency when waiting times are growing too long.  For example; when waitlists grow to a certain point you may want to consider adding new staff to accept another caseload.

Formulas Used

Days Between Referral and Today = DATEDIFF('Chart Data - Sample'[Date - Referral].[Date],TODAY(),DAY)
*Reminder that formulas will have to use fields from your own data set. eg 'Your Table'[Your Field]

If you have any questions post them in the comments below.

For more video tutorials about reporting Behavioral and Mental Health Data: Click Here >>