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
rcascarano
New Member

Visualize/quantify gaps in time between successive start and end times for IDs with multiple records

I am collecting data from multiple devices and need some way of visualizing the "data gaps" between runs. For example, generally these devices stop logging data for just a few minutes in between runs, but I need to know if there was a larger than acceptable gap in data collection.

 

I've tried everything I can come up with, but cannot seem to come up with a good solution for this. My initial thought was a gantt visual and grouping the data, but the Microsoft Gantt 2.2.3 visual seeme to mess up the times and not visualize the data properly (every record came out to be a perfect 24 hours).

 

Example Data:

 

DeviceStart TimeEnd Time
ID1

2/4/2023 8:00

2/5/2023 17:00

ID1

2/5/2023 17:15

2/5/2023 20:30

ID1

2/6/2023 5:30

2/6/2023 11:15

ID1

2/6/2023 11:25

2/7/2023 1:15

ID2

2/5/2023 16:00

2/5/2023 23:45

ID2

2/5/2023 23:55

2/6/2023 3:30

5 REPLIES 5
Anonymous
Not applicable

There is a visual setting for date format. I would use that to try to get it to display the minutes - I think it's just buggy; it won't follow your date formatting column settings either. It wants a date format similar to this in the settings box (but I've never tried getting it to display time before, just date):

MM/df/YYYY hh:mm

 If you have a measure or calc column that gives the actual duration between dates that you want to highlight, you can pre-filter on that or make a bookmark. I made a video doing something semi-similar here (but not for time gaps, just expiring soon): 

https://youtu.be/fgCWlXLu-c4

Thanks. I've done something similar with the Gantt visual in the past (showing records of same type in the same row using start and end dates), but it seems like it doesn't behave the same with time in the equation.

 

Attached is an example of some of my data. You can see that the record with the inset shown isn't displaying correctly (the actual data is 10 pm to 11:21 am, but it is showing 24 hours beginning at 10 am). This is occurring across records and causing a lot of weird overlap that doesn't exist.

 

Data types are correct, etc. Have calculated duration and used that instead of end date and same problem occurs. 

 

Any thoughts? Can anyone confirm/deny if Gantt visual just does not properly support time?

 

IncorrectDateTimeGanttPBI.png

Anonymous
Not applicable

Looks like not, but there are some solutions in this thread that might work for you: https://community.powerbi.com/t5/Desktop/24-hour-based-gantt/td-p/83892 

Thanks. I had come across that thread but I think the granularity of the time data I have makes it a bit too cumbersome. I've come up with some another solution to sort of solve my problem... calculating the duration difference between successive records and conditionally formatting those that are greater than we'd like.

 

Not quite what I was shooting for with the Gantt visual, but works well enough.

Anonymous
Not applicable

Yeah, calculating the difference and making a bookmark button that filters for > whatever threshold maybe. The conditional formatting "bars" in tables seem to stand out really well as a size indication. 

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.