KPI report of Massachusetts General Hospital

Project description

To build a high-level KPI report for the executive team, based on a subset of patient records. The purpose of the report is to give stakeholders visibility into the hospital’s recent performance.

The dashboard will be designed to scale and accommodate new data over time. Additionally, the CEO has requested a summary of any insights derived from the provided sample.

Project Goals

• Develop a comprehensive KPI report to give the executive team a clear and concise view of the hospital’s recent performance using key patient metrics.

• Accurately track and report the number of patient admissions and readmissions over time to identify trends and inform decision-making.

• Calculate and display the average length of stay for patients to assess operational efficiency and identify areas for process improvement.

• Determine the average cost per visit to understand the financial implications of patient care and identify cost-saving opportunities.

• Report on the number of procedures covered by insurance to gauge financial risk.

• Equip stakeholders with accurate and timely data to support data-driven decision-making processes and improve overall hospital management.

Key Business Needs

• Implement a reliable system for tracking key performance metrics such as patient admissions, readmissions, length of stay, and cost per visit, enabling timely and informed decision-making.

• Gain insights into the average cost per visit to identify potential cost-saving opportunities and manage the hospital's financial performance effectively.

• Assess the extent of insurance coverage for procedures to understand financial risk and negotiate better terms with insurance providers.

About the Dataset:

The data set consists of five table: Encounter, Patient, Organization, Payers and Procedures.

First we need to build the relationships between the tables and also connect the table with calendar, which have been created for the use of dynamic KPI cards.

Few measures need to be created for the analysis of the dataset.

1.  Number of readmitted patients

2.  Length of Stays (In hours)

3.  Average cost per visit

4.  No. Of procedures covered under Insurance

5.  Insurance Coverage

Let's discuss the length of stay (LOS):

First we need to create a column in the Encounter table:

LengthOfStayHour = DATEDIFF(encounters[START Date], encounters[STOP Date], HOUR)

Then calculate the average stay:

AverageLengthOfStay = AVERAGE(encounters[LengthOfStay]) 

Create two measure for LOS for current and previous year:

CY LOS = 
VAR CurrentYearLOS = [AverageLengthOfStayHour]

RETURN
CurrentYearLOS

PY LOS = 
VAR currentYearLOS = [AverageLengthOfStayHour]
     VAR PreviousYearLOS =
         CALCULATE([AverageLengthOfStayHour],
        SAMEPERIODLASTYEAR('Calendar'[Date])
)
RETURN
PreviousYearLOS

Create measure for LOS growth Rate:

LOS Growth Rate = 
VAR CurrentYearLOS = [AverageLengthOfStayHour]
     VAR PreviousYearLOS =
         CALCULATE([AverageLengthOfStayHour],
        SAMEPERIODLASTYEAR('Calendar'[Date])
)
VAR Growth = 
       CurrentYearLOS - PreviousYearLOS

Var GrowthPer = 
       DIVIDE(Growth, PreviousYearLOS)
RETURN
GrowthPer

Then we can create the KPI card for LOS based on the above calculation. As we created a dynamic measurer, so that while changing the year or month, we can get the respective data for that particular year or month. I have also created dynamic titles for the year and KPI colour (for up and down growth). Similarly, i have created measures which are needed for this report.

With changes in the year, we can get the growth rate, current and previous year data.

Based on the data and analysis, I have provided the top 4 insights and solutions in the Dashboard. Please look into the Insight tab in the above report.

Hope you like the report. Please spend some of your time and give feedback on my report.

Thank You.