I'm quite lost. I've spent now few hours browsing google, trying to find correct keywords, but alas I failed.
The question is: How many times was hour, e.g. 11:00 AM, peak hour during the time period.
My data are entrances and exits of vehicles from parking zone.
I've already done measure for maximum amount of cars during the day/hour. So I know that at 11:00AM there was most cars inside parking zone during whole time period. But I don't know whether 11:00AM was peak hour most of the time during that period.
It seems so easy, yet, I can't find/think of solution.
Hopefully I make sense, since english is not my first language.
Thanks for any help!
Ok I guess I can also provide some sample data
This is the result I currently have (based on my previous message). Column amount of cars is measure ('kolko aut o tomto case') not calculated column, but to ilustrate I show it as column.
|Date||Hour||Amount of cars|
|Hour||How many times peak hour|
Now i know 11:00 was the peak hour most time.
Maybe screenshots and formulas will be enough?
Source data 'BLU Short terms' - from parking database, how car moved through different zones in underground parking (ticket_id may be repeated)
This I summarized by ticket_id with MIN on entry_time and MAX on exit_time (I also roundeddown these times to hour). I also CONCATENATEX entry_zone and exit_zone, so I could remove cars which moved through multiple zones. Only desired route was entry_zone = 1 and exit_zone = 0. I also did some cleaning by removing cars which were only for short time inside parking. formula is as follows (sry I don't know how to properly format here, so just using dax studio to format).
Note: I tried grouping data in Power Query but there was error on load, that's why i decided to summarize it in DAX.
And created two measures
1. one for how many cars where at the DateTime in parking zone
kolko aut o tomto case =
VAR tmpDateTime =
SELECTEDVALUE ( DateTime[DateTime] )
SELECTEDVALUE ( DateTime[Entity] );
+ COUNTROWS (
'Blumental in out';
'Blumental in out'[Vstup rounded] <= tmpDateTime
&& 'Blumental in out'[Výstup rounded] >= tmpDateTime
+ COUNTROWS (
'Einpark in out';
'Einpark in out'[Vstup rounded] <= tmpDateTime
&& 'Einpark in out'[Výstup rounded] >= tmpDateTime
2.and second measure to find max value for day or hour based on visualisation
max aut = MAXX ( DateTime; [kolko aut o tomto case] )
And now I need which hour from DateTime table was peak hour most of the time.
Is it enough information?