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?
Click here to read more about the December 2021 Updates!
Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.
Mark your calendars and join us for our next Power BI Dev Camp!