cancel
Showing results for 
Search instead for 
Did you mean: 
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.






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
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors