__STYLES__

Maven Hospital Record Analysis

Tools used in this project
Maven Hospital Record Analysis

Power BI Dashboard

About this project

Understanding our patients' demographics and geographical distribution is crucial, as it provides insights into their needs and challenges. Equally important as analyzing seasonal trends in disease patterns within the medical domain, which helps us anticipate and prepare for peak periods of specific illnesses.

The objective is to provide a high-level KPI report for executives based on patient records regarding recent performance.

My analysis focuses on KPI performance compared to the previous year and addresses the following questions, accompanied by a self-explanatory dashboard:

  • I have drafted following questions to lead my analysis
  • What are the trends in the current and previous years?
  • Where are our customers coming from, and who are they, including their encounter types?
  • What is the trend in readmitted patients and their encounter types?
  • What are the trends in encounter frequency and encounter duration?
  • What is the average number of encounters per patient?
  • How do procedures compare to the previous year in terms of trends?
  • What are the reasons for encounters and procedures?
  • What are our busiest hours and days based on encounters and procedures?
  • What is the trend in insurance claim amounts?
  • How is procedures coverage trending?
  • What is the breakdown of insurance claim amounts and percentages by payers?
  • What are the common reasons for insurance claims?

This report aims to provide clear insights into healthcare operations and performance metrics, supporting data-driven decision-making and continuous improvement strategies.

The Dasboard provides Low to No Interactivity.

Technical Part:

It took me some time to understand the keywords since this is my first project in the Medical Domain. Forming relations between the tables to create the semantic model was crucial.

Data Transformation:

The dataset provided with correct data types, requiring minimal cleaning. However, I removed the "Stop" column from Encounters and Procedures after obtaining the "Start Time" and "Duration".

Details of the Model:

I created a dynamic DAX date table spanning from the minimum date to the maximum date from the Encounter tables to accommodate future refreshes (1/1/2011 - 31/12/2022).

The initial model setup was based on the provided tables. undefined

To analyze the frequency of patient visits per year (Encounters in a year), I created summarized and several disconnected tables during the analytical process.

The final model is structured to facilitate filtering by year. To ensure clarity, I created a year table using unique years up until the last completed year (if the current year's last month number is 12, return the current year; otherwise, return the previous year).

To categorize visit times effectively, I created the EncounterTimes&Order table to establish custom bins based on Encounter times. This is the final result : undefined

Additional project images

Encounters and Procedures
Busy hours and days
Insurance Coverage details
Discussion and feedback(2 comments)
comment-1676-avatar
Emmanuel Agun
Emmanuel Agun
2 months ago
Hi Balu, nice Dashboard I have a question, how do you get the “Average Length of stay ” and the “Average patient visit” mostly the “Stay Duration” page I will be glad, if I get I a swift reply Thanks

comment-1684-avatar
Balu Chelluri
Balu Chelluri
Project owner
2 months ago
Project owner
The Average length is calculated based on Encounter dim table. (We were provided start and encounter duration). For the Average patient visits I have summarized the encounters based on the yearly for each encounter type. (A patient can have multiple appointment for the same treatment). Used the summarised table to create the histogram for encounters. Hope this is useful.
2000 characters remaining
Cookie SettingsWe use cookies to enhance your experience, analyze site traffic and deliver personalized content. Read our Privacy Policy.