Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |