Forum Discussion

Drewz's avatar
Drewz
Helper II
6 years ago

Dynamically Find Peak Hour Time

Hi All, I've been searching the internet and the Power BI community for ages and I can't find anything that matches what I'm trying to do, so I'm stumped.    I have intersection vehicle count data...
Drewz's avatar
Drewz
Helper II
6 years ago

Hi Greg_Deckler ,

Thanks for the updated pbix. The tricky part that is that the hour when the traffic volume is highest may not be 7 am to 7:59 am (e.g all the hours that start with 7). It might be from 7:45 am to 8:44 am, and therefore my calculation would need to work out to sum the values at 7:45, 8:00, 8:15 and 8:30 am. And each site will have a different peak hour, so I want to dynamically calculate when the peak hour occured depending on what site is selected using a slicer. If I can dynamically work out when the peak our occurs, then I'd be able to create a slicer to show either peak hour count data or all count data.

 

Did my explanation help?

 

Thanks for the tip about complex selectors. I had a quick look, but I'll need some time to study them to understand how they work.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

Drewz - I've been giving this some thought. If your data comes in every 15 minutes, you should be able to have a table of your times in 15 minute increments. If you use that in your visual, you should be able to construct a measure that takes that time and filters your fact table for >= MAX('TimeTable'[time]) && <= (MAX('TimeTable'[time]) + 1/24)

 

I'm thinking that should work.

  • Drewz's avatar
    Drewz
    Helper II
    6 years ago

    Hi Greg_Deckler,

    I made a bit of progress, but I'm stuck again. Here's a link to my updated pbix file.

     

    I wasn't sure how to progress your suggestion with the MAX filters specifically, but I have a separate TimeTable as suggested and I used the following measure to give me running total in hours. 

     

    RollingHourlyVehicles = Var FromTimeID = SELECTEDVALUE(TimeTable[TimeIndex])
    Var ToTimeID = FromTimeID + 3
    Return CALCULATE([Total Vehicles],
    FILTER(ALL('TimeTable'), TimeTable[TimeIndex]>=FromTimeID && TimeTable[TimeIndex]<=ToTimeID)
    )
     

    and the following measure to give me the peak hour

    PeakHour = CALCULATE(MAXX(VALUES(TimeTable[Actual Time]), [RollingHourlyVehicles]))

     

    Only problem is that in a card the peak hour is a single value of the highest count (which is what I want), but in a table context it is different for each time. How do I extract the peakhour as a single value? e.g. just as the number 4009. I can't figure out how to get the max value of a measure. Next step, I can see that the peak hour starts at 7:45 am (it counts the traffic data for 7:45, 8:00, 7:15 and 7:30 am). But how would I extract the peak hour time as a value so that I can use it as a filter on my data?  

     

     

    Any help greatfully received as I've been battling this for hours.