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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Fuel_UK
Frequent Visitor

Count of Two Date/Time Columns by Interval

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

Fuel_UK_0-1675716206935.png

For illustrative purposes the desired output should look as below:

Fuel_UK_2-1675716407693.png

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!

1 REPLY 1
v-jianboli-msft
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

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors