From Descriptive to Predictive- Building an Aviation Dashboard in Power BI: Insights with DAX

Author(s):

Originally published on Towards AI.

Harnessing data to optimize airline performance during weather conditions

Introduction

Although machine learning models are capable of forecasting flight delays, their basis is a thorough comprehension of past trends. This manual concentrates on exploratory data analysis, a crucial phase in any data pipeline. Moving beyond simple visuals, we’ll use DAX in Power BI to create an interactive dashboard that conducts sophisticated feature analysis and reveals the fascinating narrative concealed in airline delay data — a crucial ability for any data scientist or analyst.

As part of this project, I made a Power BI dashboard to look at how weather affects flight operations. The dashboard shows a number of key performance indicators (KPIs), such as cancellations, delays, and customer satisfaction for all types of travel.

But here’s the cool part: the dashboard gives you descriptive analytics, but it’s really useful when you combine it with AI and predictive modeling. Let’s talk about how I made it and what the next step might be.

Why Aviation Analytics Matters:

• Flight performance analysis: Identify delay patterns and on-time performance.
• Passenger trends: Track seat occupancy and route profitability.
• Operational cost optimization: Highlight key expense areas to reduce overhead.

1.Dataset Overview

The dataset has columns that look like this:
> FlightDate, Airline, Departure and Arrival Location
> Status (Cancelled, On-time, Delayed), DelayMinutes, and WeatherImpact

Goal: Look into what causes delays, how they happen by time and airline, and how weather affects flight movements.

2. Data Cleaning and Transformation

> Removed unnecessary columns and duplicate values.
> Created calculated columns named ‘Weather_Impacted_Label’ and ‘Cancellation_Label’ for delay category.

Weather_Impact_Label = 
IF(Flights_Dataset[Weather_Impact] = 1, "Weather Impact", "No Weather Impact")

Cancellation_Label = 
IF(Flights_Dataset[Flight_Status] = "Cancelled","Cancelled","Not Cancelled")

Note: The values 1 and 0 indicate whether a flight was impacted by weather conditions (1 = Yes, 0 = No).

Step 3: Preparing the Dashboard

The dashboard was meant to offer real-time and relevant information in regards to the performance of the airline. The major visual elements were:

KPI Cards:
> Total Flights: This measure gives a general idea of the size of the dataset.
> Mean Delay: This measure helps in tracking the on-time performance of all flights.
> Total Revenue — Critical in assessing how financially sound an organization is.
> The bar chart, “Top Airlines by Revenue”, represents the highest revenue-generating airlines and thus a clearer picture of which airlines are most profitable overall.
> Line Chart (Monthly Flight Performance Trend): This is a time-series analysis chart that shows trends or seasonality of the operational performance and delays in different months.
> Map Visualization (Flights by Route): This feature displays the flight locations and frequencies, as well as areas for possible business expansion.

Key Visual Insights:
> Passenger Satisfaction by Weather Condition (Bar Chart)
This is a comparison of average passenger satisfaction scores on weather-affected flights and unaffected flights.
Notably, the gap is small (7.0 vs 6.8), which indicates that airlines preserve level of service regardless of conditions.

> Bad Weather Cancellation (Donut Chart)
This graph indicates the percentage of canceled flights due to adverse weather versus non-canceled flights.
In the data, 5.3% of the flights were canceled and 94.7% proceeded as planned, a measure of operational robustness towards weather adversity.

Step 4: Adding DAX Measures

I will be selecting the top 5 DAX measures which is essential to design visualizations for inclusivity.

> Average_Delay(Weather_Impact)
It calculates the average delay in minutes for flights impacted by weather.

Average_Delay(Weather_Impact) = 
 CALCULATE(
 AVERAGE('Flights_Dataset'[Delay_Minutes]),
 Flights_Dataset[Weather_Impact] = 1)

> Average_Flight_Satisfaction_Score
Shows overall passenger satisfaction score for flights.

Average_Flight_Satisfaction_Score = 
 CALCULATE(
 (AVERAGE(Flights_Dataset[Flight_Satisfaction_Score])))

> Total_Flights(Weather_Impact)
This measure counts the total number of flights affected by weather conditions.

Total_Flights(Weather_Impact) = 
 CALCULATE(
 COUNTROWS('Flights_Dataset'),
 Flights_Dataset[Weather_Impact] = 1 )

> No_show_Rate%(Weather_Impact)
This measure uses variables (VAR) and DIVIDE function for better readability and handles division by zero.

No-Show_Rate%(Weather_Impact) = 
VAR TotalFlights =
 CALCULATE (
 COUNTROWS ( 'Flights_Dataset' ),
 'Flights_Dataset'[Weather_Impact] = 1
 )
VAR NoShowFlights =
 CALCULATE (
 COUNTROWS ( 'Flights_Dataset' ),
 'Flights_Dataset'[Weather_Impact] = 1,
 'Flights_Dataset'[No_Show] = 1
 )
RETURN
 DIVIDE ( NoShowFlights, TotalFlights, 0 )

> On_Time%(Weather_Impact)
Calculates the percentage of flights affected by weather that were not canceled (i.e., completed on time or with minimal delay).

On-Time%(Weather_Impact) = 
VAR TotalFlights = 
 CALCULATE(COUNTROWS('Flights_Dataset'), Flights_Dataset[Weather_Impact] = 1)
VAR OnTimeFlights = 
 CALCULATE(COUNTROWS('Flights_Dataset'),
 Flights_Dataset[Weather_Impact] = 1,
 Flights_Dataset[Delay_Minutes] <= 15
)
RETURN
DIVIDE(OnTimeFlights, [Total_Flights])

Step 5: Insights and Analysis

The dashboard allows us to:
> Identify best-performing airlines.
> Look for timing patterns between earnings and delays.
> Identify how the weather impacts operations.

Power BI offers filters by which decision-makers can examine in detail certain times, routes, or airlines for more information.

Forecasting and Predictive Analysis

> Aviation is inherently unpredictable, but that doesn’t mean we have to be unprepared. The key to building resilience is leveraging our historical data to anticipate future performance.
> This dashboard isn’t just a record of the past; it’s a foundation for predictive modeling.
> Data scientists can use this infrastructure to build models that anticipate demand fluctuations and long-term trends. For decision-makers, these forecasts are crucial, illuminating both potential risks on the horizon and opportunities for growth.
> Our analysis, as shown in the image, provides the insights needed to develop robust strategies for adapting to an ever-changing market.

Conclusion

By using historical air traffic data and predictive analytics, airlines can gain a better understanding of the effects of weather on air operations and predict potential disruptions. Insights derived from forecasting enable aviation professionals to understand risks, schedule optimally, and anticipate and manage delays and operational issues proactively. Lastly, data analytics usage in decision-making enables the industry to accommodate dynamic weather, enhance passenger experience, and ensure efficiency in the presence of a volatile operating environment.

Thank you for reading my article…

Join thousands of data leaders on the AI newsletter. Join over 80,000 subscribers and keep up to date with the latest developments in AI. From research to projects and ideas. If you are building an AI startup, an AI-related product, or a service, we invite you to consider becoming a sponsor.

Published via Towards AI