Critical Care & ER Analytics Dashboard

My role

BI Consultant & Data Analyst — solo end-to-end from raw patient data to a live operational dashboard

The challenge

An emergency department had no consolidated way to monitor patient flow, wait times or admission patterns over time. Staff could see daily activity in isolation, but there was no way to answer which hours saw the heaviest patient load, whether wait time targets were being met, or how admission rates broke down by age, gender or referring department. Staffing and scheduling decisions were being made without a real data foundation.

The data problem

Patient records existed in two disconnected places: an Excel export covering registration-level detail (demographics, admission status, wait times, department referrals) and a separate SQL Server database holding related operational records. Neither source alone gave a full picture, and there was no shared structure between them to support time-based analysis like day-of-week or hour-of-day volume, or month-over-month trending.

My approach

I pulled the SQL Server data and combined it with the Excel export in Power Query, aligning patient records across both sources into a single consistent model. That meant resolving overlapping fields, standardizing date and time formatting so hourly and daily patterns could actually be calculated, and building categorical groupings for age brackets, admission status and department referral that weren't consistently structured in the raw data.

Once unified, I modeled the data to support three views built around how ER staff and administrators actually work: a Consolidated Breakdown page for the full historical view, a Monthly Breakdown page using sparkline-style trend visuals so a single month's daily fluctuation in volume, wait time and satisfaction can be scanned at a glance, and a Patient Information page as a searchable, filterable record-level table for operational or audit lookups.

The day-and-hour heatmap was the most technically involved piece. I built DAX measures aggregating patient counts into two-hour blocks across each day of the week, then applied conditional formatting so staffing-relevant patterns are visible immediately without reading raw numbers. I also built a wait-time-target measure calculating the percentage of patients seen within a 30-minute benchmark, tracked as its own KPI rather than buried inside average wait time — because an average can hide how often the target is actually being missed.

The result

The dashboard gave ER leadership a live, unified view across previously siloed systems. It surfaced that only 40.68% of patients were seen within the 30-minute target despite a reasonable-looking average wait of 35.3 minutes — showing the average was masking a meaningful share of patients waiting well past the benchmark. It also identified Saturday as the highest patient volume day and the 30–39 age group as the largest single patient segment by a wide margin, both directly useful for staffing and resource planning.

Tools & tech stack

• SQL ServerSource for operational patient records, queried and pulled alongside the Excel dataset
• Power Query (M)Combining and reconciling the Excel export with SQL Server data into one consistent patient model
• Power BI & DAXWait-time-target calculations, time-based aggregations (day/hour heatmap), trend measures
• Conditional formattingApplied to the day-and-hour patient volume table for pattern visibility