Hello,
i saw your really helpful videos on YouTube and wanted to ask for your help in excel issue. The data basis that i need to analyse is an historical flight plan of an airport. That means I have a flight plan with each flight of the day and the arrival time. What I wanted to know is the rolling peak hour of arrivals in five minutes time window. That means the numbers of flights from e.g. 1pm to 2pm or 1:25pm to 2:25 or 1:40 to 2:40 and so far. All those “rolling hours” needs to be plotted in a day chart so I can find the “max. peak hour” of the day. It would be also great to filter the flight of the day by e.g. airline or departure destination and get also the rolling peak hour and max. peak hour of the day. I would be vary thankful if you try to help me and show me the right way to do that in excel or Power Bi.
Thank you so much for your time hope to hear from you
with best regards
Sven
Hi Max,
Please find the attached file that summarises the data in a PivotTable (chart sheet), then uses SUMIFS to aggregate the data into a rolling 5 minute hour (Chart Data table in columns D:F). The Slicers allow you to control what values the PivotTable returns, which in turn filters the Chart Data and chart.
Not sure you can do this (efficiently) with Power BI/Power Pivot because they don't have time intelligence functions for times (only available for dates).
Mynda
Hi Mynda, thank you so much. thats exactly what i need to get. 🙂
Hi Mynda,
after analysing the data i found some wrong sums. I hightlighted the area in the attache file. The SUMIFS in H14 should shows 1.283 Pax (sum from passengers from 00:55:00 - 01:50:00) but it shows only 1.095 ? The most sumifs are correct but some are wrong. What is the matter for that and can you help?
Thanks a lot again.
Hi Max,
That file you attached (rolling-hour_peak-hour-2.xlsx) doesn't have any formulas in it.
Mynda
