Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mpetrilaksb
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
mpetrilaksb
Frequent Visitor

Solved, and even optimized.

parry2k
Super User
Super User

@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.

DateHourAmount of cars
5.1.20219:005
5.1.202110:006
5.1.202111:004
6.1.20219:004
6.1.202110:005
6.1.202111:006
7.1.20219:007
7.1.202110:0010
7.1.202111:0015
8.1.20219:009
8.1.202110:008
8.1.202111:0012
9.1.20219:003
9.1.202110:009
9.1.202111:008
10.1.20219:008
10.1.202110:0010
10.1.202111:0011

 

Intermediate step 

DatePeak hour
5.1.202110:00
6.1.202111:00
7.1.202111:00
8.1.202111:00
9.1.202110:00
10.1.202111:00

 

Wanted result.

HourHow many times peak hour
9:000
10:002
11:004

 

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)

mpetrilaksb_0-1624952801435.png

 

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 =
    ADDCOLUMNS (
        ADDCOLUMNS (
            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:
mpetrilaksb_1-1624956481252.png

 


Then i created datetime table date, hour and entity (I have data from two parking places)
mpetrilaksb_2-1624956560574.png

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.