I need a project on power bi regarding health census. Data and problem description will be provided.
Budget: ₹600 – ₹1,500 INR
Organizational Context
The organization is a multi-location healthcare system operating across:
Multiple Hospitals / Locations (e.g., Janesville, Crystal Lake, Harvard, Riverside)
Multiple Service Lines (Inpatient, ED, NICU, Observation, Surgeries, Urgent Care)
Multiple Census Metrics
Patient Days
Average Daily Census (ADC)
Discharges
Observation Count
ED Visits
Surgical Cases
Length of Stay (ALOS, GMLOS)
Census data is captured daily at the patient level and aggregated into daily, monthly, and fiscal summaries.
The dataset is continuously growing, with new records appended daily, and historical data spanning multiple years.
Core Analytical Challenge
Hospital leadership requires a single Power BI solution that can:
Accurately track current operational load
Compare performance against Budget, Prior Month, Prior Year, and Prior FY
Support real-time month-to-date analysis
Allow deep drill-down from enterprise → location → day → patient
Remain mathematically correct despite partial-month data availability
Primary Objective
Design a dynamic, multi-page Census Analytics dashboard that:
Uses daily-refreshing data
Adapts calculations automatically based on how many days of data exist
Supports cross-page navigation and drill-through
Maintains metric integrity across all levels of aggregation
All calculations must be driven from a single semantic model.
Critical Calculation Constraint (Non-Negotiable)
Dynamic Month-to-Date Logic
For the current month:
Metrics must be calculated only using the number of days actually loaded
No assumptions of full month (30/31 days)
No hardcoded divisors
No calendar-based averages unless data exists
Example Constraint (Implicit, Not Explained to Interns)
Average Daily Census must be:
Total Patient Days ÷ Number of days with available data
Not:
Total Patient Days ÷ Total days in month
All DAX must adapt automatically as new daily data is ingested.
Dashboard Architecture & Pages
1. Census Overview Page (Primary Landing Page)
Audience: Executive Leadership, Hospital Administrators
Access: All users
Functional Requirements
Global slicers for:
Service Line
Location
Time Frame (Current Month, Last 12 Months, FY)
Monthly Patient Days trend visualization
Conditional indicators:
Under Budget
Exceeds Budget
Change vs Prior Year
KPI cards showing:
Current Month
Fiscal Year to Date
Variance vs Budget
Variance vs Prior Year
Interactive Requirement
Clicking on any monthly bar must:
Dynamically reveal daily census distribution for that month
Automatically adjust calculations for partial or full months
Preserve all slicer selections
2. Trends Page
Audience: Operations Leadership
Functional Requirements
Year-over-Year Census comparison
Side-by-side comparison of:
Current Year vs Prior Year
Tabular breakdowns:
By Service Line
By Location
Display:
Absolute variance
Percentage variance
Directional indicators
Technical Complexity
YoY comparisons must align by month and service line
Missing data months must not distort trends
Totals must reconcile exactly with overview page
By Service Line
By Location
Display:
Absolute variance
Percentage variance
Directional indicators
Technical Complexity
YoY comparisons must align by month and service line
Missing data months must not distort trends
Totals must reconcile exactly with overview page
3. Length of Stay (LOS) Trend Page
Audience: Clinical & Quality Teams
Functional Requirements
Monthly trend of:
ALOS
GMLOS
Comparison across time
Visual emphasis on divergence between expected and actual LOS
Technical Complexity
LOS metrics must:
Be weighted correctly
Never be averaged naïvely
Recalculate properly for partial months
4. Location-Level Tabular Views
Pages
Location (Current Month)
Location (Prior Month)
Audience: Hospital & Site Managers
Functional Requirements
Location → Metric matrix
Metrics include:
Actual
Budget
Prior Month
Prior Year
Variances
Expandable hierarchical layout:
Location
Metric Group
Metric
Technical Complexity
Variance calculations must:
Respect selected time context
Remain accurate for partial months
Budget FYTD must not respond to daily-level filters
5. Enterprise-Level Tabular Views
Pages
Enterprise (Current Month)
Enterprise (Prior Month)
Audience: System Leadership
Functional Requirements
Aggregated view across all locations
Month and FYTD comparisons
Consistent metric definitions across all pages
Technical Complexity
Enterprise totals must:
Match sum of locations
Match overview KPIs
Match daily-level aggregation
6. Daily Census Data Page
Audience: Analysts, Power Users
Functional Requirements
Date-level table showing:
Average Daily Census
Discharges
Observation Count
ED Visits
Surgical Cases
Urgent Care
Must dynamically update based on selected month
Interactive Requirement
Right-click on any date must enable drill-through to Patient-Level Data
7. Patient-Level Data Page
Audience: Clinical Operations, Audit Teams
Functional Requirements
Patient-level detail including:
MRN
Encounter ID
Department
Length of Stay
Service classification flags
Context preserved from:
Location
Date
Service Line
Totals must reconcile back to daily census numbers
The organization is a multi-location healthcare system operating across:
Multiple Hospitals / Locations (e.g., Janesville, Crystal Lake, Harvard, Riverside)
Multiple Service Lines (Inpatient, ED, NICU, Observation, Surgeries, Urgent Care)
Multiple Census Metrics
Patient Days
Average Daily Census (ADC)
Discharges
Observation Count
ED Visits
Surgical Cases
Length of Stay (ALOS, GMLOS)
Census data is captured daily at the patient level and aggregated into daily, monthly, and fiscal summaries.
The dataset is continuously growing, with new records appended daily, and historical data spanning multiple years.
Core Analytical Challenge
Hospital leadership requires a single Power BI solution that can:
Accurately track current operational load
Compare performance against Budget, Prior Month, Prior Year, and Prior FY
Support real-time month-to-date analysis
Allow deep drill-down from enterprise → location → day → patient
Remain mathematically correct despite partial-month data availability
Primary Objective
Design a dynamic, multi-page Census Analytics dashboard that:
Uses daily-refreshing data
Adapts calculations automatically based on how many days of data exist
Supports cross-page navigation and drill-through
Maintains metric integrity across all levels of aggregation
All calculations must be driven from a single semantic model.
Critical Calculation Constraint (Non-Negotiable)
Dynamic Month-to-Date Logic
For the current month:
Metrics must be calculated only using the number of days actually loaded
No assumptions of full month (30/31 days)
No hardcoded divisors
No calendar-based averages unless data exists
Example Constraint (Implicit, Not Explained to Interns)
Average Daily Census must be:
Total Patient Days ÷ Number of days with available data
Not:
Total Patient Days ÷ Total days in month
All DAX must adapt automatically as new daily data is ingested.
Dashboard Architecture & Pages
1. Census Overview Page (Primary Landing Page)
Audience: Executive Leadership, Hospital Administrators
Access: All users
Functional Requirements
Global slicers for:
Service Line
Location
Time Frame (Current Month, Last 12 Months, FY)
Monthly Patient Days trend visualization
Conditional indicators:
Under Budget
Exceeds Budget
Change vs Prior Year
KPI cards showing:
Current Month
Fiscal Year to Date
Variance vs Budget
Variance vs Prior Year
Interactive Requirement
Clicking on any monthly bar must:
Dynamically reveal daily census distribution for that month
Automatically adjust calculations for partial or full months
Preserve all slicer selections
2. Trends Page
Audience: Operations Leadership
Functional Requirements
Year-over-Year Census comparison
Side-by-side comparison of:
Current Year vs Prior Year
Tabular breakdowns:
By Service Line
By Location
Display:
Absolute variance
Percentage variance
Directional indicators
Technical Complexity
YoY comparisons must align by month and service line
Missing data months must not distort trends
Totals must reconcile exactly with overview page
By Service Line
By Location
Display:
Absolute variance
Percentage variance
Directional indicators
Technical Complexity
YoY comparisons must align by month and service line
Missing data months must not distort trends
Totals must reconcile exactly with overview page
3. Length of Stay (LOS) Trend Page
Audience: Clinical & Quality Teams
Functional Requirements
Monthly trend of:
ALOS
GMLOS
Comparison across time
Visual emphasis on divergence between expected and actual LOS
Technical Complexity
LOS metrics must:
Be weighted correctly
Never be averaged naïvely
Recalculate properly for partial months
4. Location-Level Tabular Views
Pages
Location (Current Month)
Location (Prior Month)
Audience: Hospital & Site Managers
Functional Requirements
Location → Metric matrix
Metrics include:
Actual
Budget
Prior Month
Prior Year
Variances
Expandable hierarchical layout:
Location
Metric Group
Metric
Technical Complexity
Variance calculations must:
Respect selected time context
Remain accurate for partial months
Budget FYTD must not respond to daily-level filters
5. Enterprise-Level Tabular Views
Pages
Enterprise (Current Month)
Enterprise (Prior Month)
Audience: System Leadership
Functional Requirements
Aggregated view across all locations
Month and FYTD comparisons
Consistent metric definitions across all pages
Technical Complexity
Enterprise totals must:
Match sum of locations
Match overview KPIs
Match daily-level aggregation
6. Daily Census Data Page
Audience: Analysts, Power Users
Functional Requirements
Date-level table showing:
Average Daily Census
Discharges
Observation Count
ED Visits
Surgical Cases
Urgent Care
Must dynamically update based on selected month
Interactive Requirement
Right-click on any date must enable drill-through to Patient-Level Data
7. Patient-Level Data Page
Audience: Clinical Operations, Audit Teams
Functional Requirements
Patient-level detail including:
MRN
Encounter ID
Department
Length of Stay
Service classification flags
Context preserved from:
Location
Date
Service Line
Totals must reconcile back to daily census numbers