__STYLES__
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
Key Business Needs
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.
Number of readmitted patients
Length of Stays (In hours)
Average cost per visit
No. Of procedures covered under Insurance
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.