Hotel Booking Data Analysis Dashboard
Lesson Overview
In this lesson, students will learn how to analyze hotel booking trends using Power BI by building interactive dashboards from real-world data. The dataset covers three years (2018, 2019, and 2020) and includes key information such as customer demographics, booking behavior, revenue, and cancellations.
Students will explore how to clean, transform, and visualize data effectively, using DAX formulas, slicers, and interactive visuals to uncover insights for hotel business performance.
Learning Objectives
By the end of this lesson, students will be able to:
-
Import and clean real-world hotel booking data.
-
Create custom columns and DAX measures in Power BI.
-
Build dynamic and interactive dashboards for each year.
-
Visualize key metrics such as cancellations, guest count, revenue, and customer types.
-
Apply filters, slicers, and design techniques for better usability.
-
Generate business insights to improve decision-making in the hotel industry.
Dataset Source
We’ll use a publicly available dataset from Kaggle: Hotel Booking Data - Kaggle
Download the Excel file. The dataset contains three sheets for the years 2018, 2019, and 2020.
Step 1: Understand the Dataset
Each sheet contains detailed booking records. Below are the important original columns:
|
Column Name |
Description |
|---|---|
|
arrival_date_year |
Year of booking |
|
arrival_date_month |
Month of arrival |
|
stays_in_weekend_nights |
Nights stayed over weekends |
|
stays_in_week_nights |
Nights stayed over weekdays |
|
adults, children, babies |
Number of guests by type |
|
adr |
Average daily rate (room revenue) |
|
is_canceled |
1 = canceled, 0 = not canceled |
|
customer_type |
Guest classification (e.g., Transient, Group) |
|
distribution_channel |
Booking channel (e.g., Direct, TA/TO) |
Step 2: Create Custom Columns in Power BI
To make the data more insightful, we’ll add custom columns using Power BI's Power Query and DAX features:
|
Custom Column/Measure |
Formula/Logic |
|---|---|
|
Total Night |
|
|
Revenue |
|
|
Person |
|
|
MonthNum |
Use number mapping for month names (e.g., Jan = 1) |
|
Cancellation Status |
If |
|
MealCost |
Use |
Step 3: Dashboard Design Structure
We will create three dashboards, one for each year (2018, 2019, and 2020), to allow both individual analysis and year-over-year comparison.
Section-wise Visuals
1. Cards
-
Total Guest Count (sum of adults + children + babies)
-
Hotel Type Filter
-
Month Selection Indicator
2. Donut Chart
-
Cancellation Status (Yes vs. No)
3. Column & Bar Charts
-
Monthly Guest Count by
arrival_date_month -
Booking Channel Performance using
distribution_channel
4. Line Chart
-
Average Lead Time by Month — shows guest booking behavior trends
5. Pie Charts
-
Monthly Revenue Contribution
-
Customer Type Breakdown
6. Slicers
-
Interactive filters for:
-
Hotel Type
-
Arrival Month
-
Market Segment
-
Step 4: DAX Measures (Examples)
Total Nights = [stays_in_weekend_nights] + [stays_in_week_nights]
Revenue = [adr] * [Total Nights]
Person = [adults] + [children] + [babies]
Cancellation Status = IF([is_canceled] = 1, "Yes", "No")
Hands-On Project: Dashboard Walkthrough
Here are snapshots of the dashboards created for each year:
📌 2018 Dashboard
📌 2019 Dashboard
📌 2020 Dashboard
Each dashboard includes consistent layout and slicer placement to enable smooth year-wise comparison.
Insights & Conclusion
Through visual analysis, students can uncover:
-
Peak guest months and seasonal demand
-
Top-performing booking channels like TA/TO or Direct
-
Revenue contribution by different customer types
-
Cancellation patterns, helping identify unreliable sources
-
Guest behavior trends across years
These insights help in revenue optimization, targeted marketing, and operational planning for hotels.
