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...
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.