Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved, and even optimized.
@mpetrilaksb it will be easier if you throw a sample pbix with expected output and will get you the solution.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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.
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] )
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?
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |