Forum Discussion

Sven01's avatar
Sven01
Icon for Helper I rankHelper I
8 years ago
Solved

rolling hour / peak hour

 

 

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

Need Help

13 Replies

    • Sven01's avatar
      Sven01
      Icon for Helper I rankHelper I
      8 years ago

      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

    • Sven01's avatar
      Sven01
      Icon for Helper I rankHelper I
      8 years ago

      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's avatar
        RobertSlattery
        Icon for Responsive Resident rankResponsive Resident
        8 years ago

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

  • v-xjiin-msft's avatar
    v-xjiin-msft
    Icon for Solution Sage rankSolution Sage
    8 years ago

    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's avatar
      Sven01
      Icon for Helper I rankHelper I
      8 years ago

      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's avatar
        v-xjiin-msft
        Icon for Solution Sage rankSolution Sage
        8 years ago

        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.