Dynamically Find Peak Hour Time

[Drewz]

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 (in 15 minute intervals) at two sites in the attached sample, but this will eventually expand to 100s of sites (Link to PowerBI file).  There is morning and afternoon peak count data. I want to be able to select a site and dyncamically calculate when the peak hour occurs for both the morning and evening periods. I then want to be able to create a slicer or button to filter by either the morning or evening peak and then update my column chart.  The peak hour is different for each intersection, so it can't be a set hour. 

And when my column chart is showing the whole count period, how can I highlight the morning and evening peak hours as I've drawn below. I've seen it done, I just can't figure out how to do it :(.

One last question. When I import time from Excel, it has the data 31/12/1899 attached to it. Just wondering if I'm doing something wrong as it prevents me from using the AUTOCALENDAR() option as it starts from 1899 even when I change the format to Time only. Although as a work around I can use AUTOCALENDAR with a start year, but just wondering if I'm doing something wrong when I import and format that initial data. 

Thanks in advance, 

Cheers Drew

[lbendlin]

Never use CALENDARAUTO - always (ALWAYS) use your own Dates table. And always disable auto date/time hierarchies.  

DateTime values without Date will start at the date boundary (12/31/1899) - it is what it is.  Use just the fractional part of the value if you want to avoid that.

Within your data, can you even say what is morning and what is afternoon?  Will you have scenarios with more than two peak times per day? 

What you describe seems to call for proper statistical tools. Maybe an R or Python script and visual can produce the required results. DAX itself is most likely not sufficient.

[Drewz]

@lbendlin ,

thanks for the tips re CALENDARAUTO and disabling date/time hierachies. I've heard this mentioned before, but haven't worked out why. But I can Google that. 

I could potentially have a midday peak as well if that data is collected, but for now my focus is just on morning and evening peaks. Baby steps. 

I wasn't sure what you mean by your question re what is morning and what is afternoon (evening). Basically the morning is within the AM time period and evening would be in the PM period. But I'm sure you figured that out, so I'm not sure what you were asking me.

I'm hoping there is a way of doing this with DAX. I just can't figure out where to start. I found some info on calculating a rolling average. I assume I could apply that to a rolling sum. Could that work? 

[Greg_Deckler]

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

[Drewz]

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.

[Greg_Deckler]

@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)

[Drewz]

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]

@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]

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. 

[Greg_Deckler]

@Drewz - Yes, this helps greatly, let me see what I can do with this additional information.

[lbendlin]

Is morning until 11am, or is it until 11:59am ? etc.

As you may have noticed by now, in BI oftentimes a chart solution is MUCH easier to achieve and to comprehend than a numbers based solution for exactly the same question.

A rolling window sum only gets you so far. Eventually the question will be a question of slope. IE - is the current value bigger or smaller than the previous one?  For example you could highlight the first bar that is bigger than its successor. That will work for perfect bell curves but will not work for your real world data with its camel humps.

I would go with a rolling average, and then apply the above test to that.