Forum Discussion
Dynamically Find Peak Hour Time
Drewz - You can set a column to Time in Power Query and it will come through to the data model in a Time format. Use Column tools and set to a Time data type and format to your desired time format.
What I do not understand is your peak and how you want that to be chosen. You cannot use a measure in a slicer but you should be able to find your peak easy enough but I need to understand more about how your data is coming in. Is it coming in in 15 minute intervals or ?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, 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
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Hi Greg_Deckler,
Thanks for your reply. I do set the column to Time in Power Query, but CALENDARAUTO still detects the 1899 date. But it seems CALENDARAUTO is not recommended, so I'll avoid using it form now on.
In my original post I added a link to my pbix file so you could see exactly what my data looks like. Did my link work? Yes it is in 15 minute intervals. So I'm guessing I'd need to do a rolling sum of 4 x 15 min counts. But even if I do that, how can I extract what hour that occurs to create a peak hour filter for the morning and evening.
Drewz - See page 2. I'm still not sure exactly what you want but I did a chart that had the time in the correct format and date as well as created an Hour column and showed it that way. If you really want to chart to essentially only show peak traffic, you would need to implement what I call a Complex Selector. You can see several examples here.
https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534
Your PBIX is attached with updates. (below sig)
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.
Drewz - Yes, this helps greatly, let me see what I can do with this additional information.