Count of Two Date/Time Columns by Interval

[Fuel_UK]

Hello, 

This might be an oversight on my end, but for the life of me i cannot solve the below issue after what feels like 100 attempts/approaches .

I have a simple two columned table called Entry_Exit.

The columns are date/time stamps.

Eg:

Entry	Exit
01/04/2021 03:32	01/04/2021 04:17
01/04/2021 04:45	01/04/2021 07:32
01/04/2021 06:56	01/05/2021 12:32
01/04/2021 07:34	01/06/2021 10:13

I now want to create a three columned table using these two columns. 
I am looking to create intervals of time (5/15/30/60/120/180mins) for a 24H review. Currently i am doing this using a switch statement for each. 

As an example, the desired output for the "180min interval" for the above dataset would look as below:

Interval	Entry Count	Exit Count
00:00 - 03:00	0	0
03:00 - 06:00	2	1
06:00 - 09:00	2	1
09:00 - 12:00	0	2
... continued until	...	...
21:00 - 23:59	0	0

Effectively we are trying to assess "congestion" and "decongestion" of "events" over a 24H period. 
This data spans a 2 year period so a secondary date table will be used as the slicer for the visual.

Ultimately the customer wants a line graph as below. Interval on the X-Axis, and count of entry and exits on the y axis respectively.
Below shows both plotted but the intervals are not considered correctly.

For illustrative purposes the desired output should look as below:

Doing an "average" over a "count" would be the second stage but i am struggling to get the count right first as i am unsure on how to create the interval column using both the Entry & Exit Column as my current count comes back as the same result for both Entry and Exit which is not correct. (IE i am only getting the Entry count and the Exit count is not calculating correctly as it is considered against the Entry timestamp)

Hopefully this all makes sense. I have attached the Data Sheet Here (although its only two columns but hopefully this may help)

Any help here would be appreciated!

[v-jianboli-msft]

Hi @Fuel_UK ,

You said that "I am looking to create intervals of time (5/15/30/60/120/180mins) for a 24H review"

But you don't have just one day of data, they are many days of data, is the number of entries and exits in different time intervals calculated based on the date selected? If you are using a slicer to select the date you want to view, is the slicer single-selected or can it be multi-selected? What is the relationship between your date table and your data table?

Sorry for that the information you have provided is not making the problem clear to me. Can you please share more details to help us clarify your scenario?

Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.