{"id":48,"date":"2026-09-30T06:52:55","date_gmt":"2026-09-30T06:52:55","guid":{"rendered":"https:\/\/www.datascientist.ca\/blog\/?p=48"},"modified":"2026-10-01T03:29:57","modified_gmt":"2026-10-01T03:29:57","slug":"power-bi-tutorial-patient-flow-dashboard-using-date-time-dax-measures","status":"publish","type":"post","link":"https:\/\/www.datascientist.ca\/blog\/power-bi-tutorial-patient-flow-dashboard-using-date-time-dax-measures\/","title":{"rendered":"Power BI Tutorial: Patient Flow Dashboard using Date, Time &#038; DAX Measures"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/www.youtube.com\/@dataliteracyofficial\" target=\"_blank\" rel=\"noopener\"><\/a><\/p>\n\n\n\n<iframe loading=\"lazy\" title=\"Emergency Room Analytics\" width=\"600\" height=\"373.5\" src=\"https:\/\/app.powerbi.com\/view?r=eyJrIjoiYzc1NzBhMzItMmU2NS00ZWRiLWJiNmMtMDlkYzVmOTNmN2E1IiwidCI6IjRhNjk1YWI3LWViNzktNDViZS05NTg2LWQ4NTA4ODUwYTY1NSJ9\" frameborder=\"0\" allowFullScreen=\"true\"><\/iframe>\n\n\n\n<figure class=\"wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<iframe loading=\"lazy\" title=\"Power BI Tutorial: Patient Flow Dashboard using Date, Time &amp; DAX Measures\" width=\"500\" height=\"281\" src=\"https:\/\/www.youtube.com\/embed\/jUQVozvVc1c?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe>\n<\/div><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Case study overview: seeing patient flow by day and hour<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>The business problem.<\/strong> 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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>The solution.<\/strong> 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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Who it is for.<\/strong> 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.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">The data: patient visit timestamps<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">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. <em>[Adjust this list to match the columns in your file.]<\/em><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">From those timestamps the dashboard can measure:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Volume:<\/strong> visits per day, weekday and hour<\/li>\n\n\n\n<li><strong>Wait time:<\/strong> minutes from arrival to being seen<\/li>\n\n\n\n<li><strong>Length of stay:<\/strong> minutes from arrival to discharge<\/li>\n\n\n\n<li><strong>Service levels:<\/strong> share of visits seen or discharged within a target time<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Modelling date and time<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Split the timestamp.<\/strong> In Power Query, create an arrival date column and an arrival time column, rounded to the minute or hour.<\/li>\n\n\n\n<li><strong>Build a Date table<\/strong> with one row per day: date, year, month number and name, quarter, weekday, and a weekend flag. Mark it as the date table.<\/li>\n\n\n\n<li><strong>Build a Time table<\/strong> with one row per hour or minute: time, hour, minute, and a time bucket such as Overnight, Morning, Afternoon and Evening.<\/li>\n\n\n\n<li><strong>Relate both to the visits table<\/strong> with one-to-many relationships on date and time keys.<\/li>\n<\/ol>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><th>Table<\/th><th>Grain<\/th><th>Used for<\/th><\/tr><tr><td>Visits<\/td><td>One row per patient visit<\/td><td>Counts, wait times, length of stay<\/td><\/tr><tr><td>Date<\/td><td>One row per day<\/td><td>Trends, weekday and month analysis<\/td><\/tr><tr><td>Time<\/td><td>One row per hour or minute<\/td><td>Peak hours, time buckets<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">If a visit has several timestamps, such as arrival and discharge, keep one active relationship for the main date and use <code>USERELATIONSHIP<\/code> in DAX for the others.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Key DAX measures<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">These measures cover volume, wait, length of stay and performance against target. Table and column names are examples, so match them to your model.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Total Visits = COUNTROWS ( Visits )\n\nAvg Wait Minutes =\nAVERAGEX (\n    Visits,\n    DATEDIFF ( Visits&#91;ArrivalDateTime], Visits&#91;SeenDateTime], MINUTE )\n)\n\nAvg Length of Stay Hours =\nDIVIDE (\n    AVERAGEX (\n        Visits,\n        DATEDIFF ( Visits&#91;ArrivalDateTime], Visits&#91;DischargeDateTime], MINUTE )\n    ),\n    60\n)\n\nVisits Seen Within Target =\nCALCULATE (\n    &#91;Total Visits],\n    FILTER (\n        Visits,\n        DATEDIFF ( Visits&#91;ArrivalDateTime], Visits&#91;SeenDateTime], MINUTE ) &lt;= 30\n    )\n)\n\n% Seen Within Target = DIVIDE ( &#91;Visits Seen Within Target], &#91;Total Visits] )\n\nVisits Last Month =\nCALCULATE ( &#91;Total Visits], DATEADD ( 'Date'&#91;Date], -1, MONTH ) )\n\nMonth over Month % =\nDIVIDE ( &#91;Total Visits] - &#91;Visits Last Month], &#91;Visits Last Month] )<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Case study overview: seeing patient flow by day and hour This Power BI dashboard shows how patients move through a [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[1],"tags":[5,13,6],"class_list":["post-48","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-healthcare-analytics","tag-power-bi-for-beginners","tag-power-query"],"_links":{"self":[{"href":"https:\/\/www.datascientist.ca\/blog\/wp-json\/wp\/v2\/posts\/48","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.datascientist.ca\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.datascientist.ca\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.datascientist.ca\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.datascientist.ca\/blog\/wp-json\/wp\/v2\/comments?post=48"}],"version-history":[{"count":2,"href":"https:\/\/www.datascientist.ca\/blog\/wp-json\/wp\/v2\/posts\/48\/revisions"}],"predecessor-version":[{"id":64,"href":"https:\/\/www.datascientist.ca\/blog\/wp-json\/wp\/v2\/posts\/48\/revisions\/64"}],"wp:attachment":[{"href":"https:\/\/www.datascientist.ca\/blog\/wp-json\/wp\/v2\/media?parent=48"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.datascientist.ca\/blog\/wp-json\/wp\/v2\/categories?post=48"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.datascientist.ca\/blog\/wp-json\/wp\/v2\/tags?post=48"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}