Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

Count of Two Date/Time Columns by Interval



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.


Entry Exit
01/04/2021 03:3201/04/2021 04:17
01/04/2021 04:4501/04/2021 07:32
01/04/2021 06:5601/05/2021 12:32
01/04/2021 07:3401/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:

IntervalEntry CountExit Count
00:00 - 03:0000
03:00 - 06:0021
06:00 - 09:0021
09:00 - 12:0002
... continued until......
21:00 - 23:5900

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!

Community Support
Community Support

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.

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors