cancel
Showing results for
Did you mean:
Frequent Visitor

## Counting how many times was hour peak hour during time period

Hi everyone,

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!

4 REPLIES 4
Frequent Visitor

Solved, and even optimized.

Super User

@mpetrilaksb it will be easier if you throw a sample pbix with expected output and will get you the solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

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 5.1.2021 9:00 5 5.1.2021 10:00 6 5.1.2021 11:00 4 6.1.2021 9:00 4 6.1.2021 10:00 5 6.1.2021 11:00 6 7.1.2021 9:00 7 7.1.2021 10:00 10 7.1.2021 11:00 15 8.1.2021 9:00 9 8.1.2021 10:00 8 8.1.2021 11:00 12 9.1.2021 9:00 3 9.1.2021 10:00 9 9.1.2021 11:00 8 10.1.2021 9:00 8 10.1.2021 10:00 10 10.1.2021 11:00 11

Intermediate step

 Date Peak hour 5.1.2021 10:00 6.1.2021 11:00 7.1.2021 11:00 8.1.2021 11:00 9.1.2021 10:00 10.1.2021 11:00

Wanted result.

 Hour How many times peak hour 9:00 0 10:00 2 11:00 4

Now i know 11:00 was the peak hour most time.

Frequent Visitor

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.

Blumental in out =
VAR tmptable =
SUMMARIZE (
'BLU Short terms';
'BLU Short terms'[ticket_id];
"ID"; MIN ( 'BLU Short terms'[id] );
"Vstup"; MIN ( 'BLU Short terms'[entry_time] );
"Vstup rounded"; FLOOR ( MIN ( 'BLU Short terms'[entry_time] ); "1:00" );
"Výstup"; MAX ( 'BLU Short terms'[exit_time] );
"Výstup rounded"; FLOOR ( MAX ( 'BLU Short terms'[exit_time] ); "1:00" );
"Suma"; SUM ( 'BLU Short terms'[payed_sum] ) / 100;
"Trasa";
CONCATENATEX (
'BLU Short terms';
'BLU Short terms'[entry_zone] & "->" & 'BLU Short terms'[exit_zone];
",";
'BLU Short terms'[id]; DESC
)
);
"Date"; DATEVALUE ( FORMAT ( [Vstup]; "DD/MM/YYYY" ) );
"Duration"; DATEDIFF ( [Vstup]; [Výstup]; SECOND ) / 3600
);
"0eur a kratsie ako 0,15h";
[Suma] = 0
&& [Duration] < 0,15
)
RETURN
FILTER ( tmptable; [Trasa] = "1->0" && [0eur a kratsie ako 0,15h] = FALSE () )

Result:

Then i created datetime table date, hour and entity (I have data from two parking places)

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] )
RETURN
SWITCH (
SELECTEDVALUE ( DateTime[Entity] );
"BLU";
0
+ COUNTROWS (
FILTER (
'Blumental in out';
'Blumental in out'[Vstup rounded] <= tmpDateTime
&& 'Blumental in out'[Výstup rounded] >= tmpDateTime
)
);
"EIN";
0
+ COUNTROWS (
FILTER (
'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?

Announcements