Case study overview: seeing patient flow by day and hour
This Power BI dashboard shows how patients move through a healthcare facility, and when congestion builds, by combining date, time of day and DAX measures.
The business problem. Hospital and clinic managers know waits feel longer at certain times, but raw visit logs do not show it. Timestamps sit in one column, so it is hard to compare weekdays with weekends, mornings with evenings, or this month with last month.
The solution. A report built on separate Date and Time tables, so every visit can be sliced by year, month, weekday, hour and time bucket. DAX measures then calculate visit volumes, wait times and length of stay against targets.
Who it is for. Hospital operations and quality teams, clinic managers, healthcare analysts, and Power BI learners who want to master date and time modelling with a realistic healthcare dataset.
The data: patient visit timestamps
The source is a patient visit table with one row per visit. A typical layout includes a visit ID, arrival date and time, triage or registration time, time seen by a provider, discharge date and time, department, and patient type. [Adjust this list to match the columns in your file.]
From those timestamps the dashboard can measure:
- Volume: visits per day, weekday and hour
- Wait time: minutes from arrival to being seen
- Length of stay: minutes from arrival to discharge
- Service levels: share of visits seen or discharged within a target time
The raw data needs preparation first. Date and time often arrive as one combined timestamp, wait times must be calculated rather than typed, and incomplete rows, such as a patient who left before being seen, need a clear rule.
Modelling date and time
The key design choice is to split date and time into separate tables. A single date-time column creates millions of unique values and makes hourly analysis slow and awkward.
- Split the timestamp. In Power Query, create an arrival date column and an arrival time column, rounded to the minute or hour.
- Build a Date table with one row per day: date, year, month number and name, quarter, weekday, and a weekend flag. Mark it as the date table.
- Build a Time table with one row per hour or minute: time, hour, minute, and a time bucket such as Overnight, Morning, Afternoon and Evening.
- Relate both to the visits table with one-to-many relationships on date and time keys.
| Table | Grain | Used for |
|---|---|---|
| Visits | One row per patient visit | Counts, wait times, length of stay |
| Date | One row per day | Trends, weekday and month analysis |
| Time | One row per hour or minute | Peak hours, time buckets |
If a visit has several timestamps, such as arrival and discharge, keep one active relationship for the main date and use USERELATIONSHIP in DAX for the others.
Key DAX measures
These measures cover volume, wait, length of stay and performance against target. Table and column names are examples, so match them to your model.
Total Visits = COUNTROWS ( Visits )
Avg Wait Minutes =
AVERAGEX (
Visits,
DATEDIFF ( Visits[ArrivalDateTime], Visits[SeenDateTime], MINUTE )
)
Avg Length of Stay Hours =
DIVIDE (
AVERAGEX (
Visits,
DATEDIFF ( Visits[ArrivalDateTime], Visits[DischargeDateTime], MINUTE )
),
60
)
Visits Seen Within Target =
CALCULATE (
[Total Visits],
FILTER (
Visits,
DATEDIFF ( Visits[ArrivalDateTime], Visits[SeenDateTime], MINUTE ) <= 30
)
)
% Seen Within Target = DIVIDE ( [Visits Seen Within Target], [Total Visits] )
Visits Last Month =
CALCULATE ( [Total Visits], DATEADD ( 'Date'[Date], -1, MONTH ) )
Month over Month % =
DIVIDE ( [Total Visits] - [Visits Last Month], [Visits Last Month] )The 30-minute target is a placeholder. Use the standard your organization follows. Exclude visits with a missing seen or discharge time before averaging, for example by calculating wait and stay minutes in Power Query or guarding each measure with a blank check, and state that rule on the report page.