rolling hour / peak hour

[Sven01]

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 5 min phases. That means the numbers of flights from e.g. 1pm to 2pm or 1:05pm to 2:05 or 1:10 to 2:10 and so far. All those "rolling hours" needs to be plotted in a day chart from 00:00 to 11:55 to 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.

Thank you so much for your time hope to hear from you

attached data file:

 Flightplan

[v-xjiin-msft]

Hi @Sven01,

To achieve your requirement, first you should know that we cannot compare time type data directly in Power BI. So in your scenario, if it is possible please add date along with your time values. Then you can try following method to get what you want:

First create an End Time column for the time range with a new calculated column.

End Time =
Timeshape[Time]
    - 1 / 24

Then we can use following measure to get the flight numbers in these time range and put it in the Values of line chart. Please be notived to remove the relationship between the two tables.

Count of Flights =
CALCULATE (
    COUNT ( Flights[Arrival/Departure] ),
    FILTER (
        ALL ( Flights ),
        Flights[Time] >= MAX ( Timeshape[Time] )
            && Flights[Time] <= MAX ( Timeshape[End Time] )
    )
)

Thanks,
Xi Jin.

[Sven01]

Hi @v-xjiin-msft thanks for your reply. May i do something wrong but cant get a result by doing your advises. Would it be possible to you to modify my data sample and provid that to me. Thank you so much

[v-xjiin-msft]

Hi @Sven01,

I have already check your sample file. 

The issue here is that there's no date in your source data. Only times. And in Power BI, we cannot compare times directly. So I suggested you to add dates along with these times. Because Power BI cannot recognize which date the time belonds to . For example, 10:30 maybe in 2018-04-22 and 11:25 maybe in 2018-04-23 or 10:30 maybe in 2018-04-23 and 11:25 maybe in 2018-04-22.

After adding the dates, then you can try my solution. So please share us a new sample where there exists datetime.

Thanks,
Xi Jin.

[Sven01]

Hi @v-xjiin-msft thanks for your reply.

I add datetimes to the new file below. I divided them in "Time", "Dates" and "Time with Dates".  But the issue with the dates is that i want only analyse the peak day of the year. So all the flights are at the same date/day (21.06.2017) but with differnt times.

The "times" in table "timeshape" are only there to represent all times of the day in 5 min phases to display also empty "times" with no flights events from "flightplan". Thats why i connected the times of "timeshape" and "flightplan".

attached the new file:

flightplan with dates

Thanks foryour help again!

[v-xjiin-msft · Accepted solution]

Hi @Sven01,

Please check the sample report. See if it works for you"

https://1drv.ms/u/s!AlqSnZZUVHmshgLUM42dhAb59BwT

Thanks,
Xi Jin.

[AirFlightUser]

Hi Xi Jin, i have the same problem as Sven. Is it possible to share your tool again? The link is unfortunately offline.

[Sven01]

@v-xjiin-msftreally really cool, thanks a lot. thats exactly what i wanna to get.

only one more question: how can i filter the flights by for example only arrivals or only departures or airline or origin destination. Is that possible altough i set "Filter( ALL('flights')" by calculating the rolling hour? What i need to know is for example the peak hour flight for only arrivals and only airline "RYR".

Thank you!

attached new file:

flightplan filter by airline

[ChandeepChhabra]

Hi @Sven01, Can you share how should the output look like along with some sample data ?

[Sven01]

@ChandeepChhabra The output should be look like that. In each rolling hour e.g. at 10:30 i can see the sum of Departures/Arrivals from 10:30 to 11:25, and at 10:35 i see the sum of Dep./Arr. from 10:35 to 11:30.

Is it possible to upload a Power Bi data file here?

Thanks a lot

[RobertSlattery]

Hi @Sven01, you an upload to your public folfer in on drive or use drop box or whatever and post a link here.

[Sven01]

@RobertSlattery thanks for help.

attached sample file:

Flightplan

[Greg_Deckler]

Sample/example data please. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Sven01]

That shows the count of Dep./Arr. at 10:30 by time

@Greg_DecklerWhat i need to know is the sum of Dep./Arr. from 10:30 to 11:25, and 10:35 to 11:30 and so far.

How can i upload that data file to you?

Thanks a lot