Create Date Time and Shortest Wait Time Measures

[PhineasT]

I am working on a dashboard that provides various Date and Time outputs.

I need to create a measure or two that will show the 'Date and Time' (in one) associated with the 'Shortest Wait Time' (in the other) during that day.

I need the same for the 'Week' and the 'Month'. 

Desired Output -

Sample Data -

TicketNumber	TicketDate	InTime	OutTime	WaitTime (hh:mm:ss)
S28	6/3/2019	7:03 AM	7:36 AM	0:33:16
S111	6/3/2019	7:03 AM
S112	6/3/2019	7:04 AM	7:28 AM	0:24:29
S210	6/3/2019	7:04 AM	1:48 PM	6:44:29
S213052	7/8/2019	2:06 PM	2:18 PM	0:11:54
S14951	7/8/2019	2:07 PM	2:22 PM	0:15:33
S213053	7/8/2019	2:07 PM	2:33 PM	0:25:47
S213054	7/8/2019	2:08 PM	2:46 PM	0:38:36
S233696	8/30/2019	4:07 PM	4:27 PM	0:20:27
S233697	8/30/2019	4:08 PM	4:34 PM	0:25:49
S110922	8/30/2019	4:08 PM
S233698	8/30/2019	4:09 PM	4:28 PM	0:19:47

Current Filter Matrix in Dashboard -

[lbendlin]

you may want to rethink your data structure. As it is it cannot handle tickets that are open across day boundaries. "Ticket Date"  should be eliminated, and "In Time"  and "Out Time" should be renamed "In DateTime" and "Out DateTime".

[PhineasT]

And then what?

Also, I don't understand your comment..."As it is it cannot handle tickets that are open across day boundaries."

I merely want a card that contains the date and time (just the date in MM-DD-YYYY form will do), and another card that contains the shortest wait time on that date and the time of day of that Shortest wait time. 

[lbendlin]

Let's say the "In Time" for a ticket is 11:55pm and the "Out Time"  is 02:05am.

[PhineasT]

Let's assume such an occurence is not possible. 

That all actions happen within a 24 hour period, with most happening between 04:00 and 20:00.

Seems I should be able to click on a calendar slicer - picking a day/date; There may be hundreds of 'Tickets' on that date/date. This action should populate card with the 'Shortest Wait Time' on that given day/date.

Is this not possible?

Something like this, except the desired ouput is 'Shortest Wait Time' rather than 'Earliest Start'.

Earliest Start =
MINX (
    CALCULATETABLE (
        Table_Roster,
        ALLEXCEPT ( Table_Roster, Table_Roster[Date], Table_Roster[Name] )
    ),
    Table_Roster[Start]
)

[lbendlin]

Create a table variable (via SUMMARIZE)  of the tickets for the current filter context, include the ticket duration  (probably ignore the tickets without end time),  and then do a MINX on that table variable.

[PhineasT]

Beyond my skill set.

Can you provide, or do you have a link to, an example of what your are recommending?

[lbendlin · Accepted solution]

[PhineasT]

Here is my first attempt.

The actual 'Shortest Wait Time' for the date chosen (Sept. 19, 2020) is 00:16.49 (hh:mm:ss); However, the measure returns 12:16:00.

Did I do something wrong, or do I need to change something so that the output is 00:16:49?

Data Source -

[lbendlin]

Use the FORMAT function to tame that datetime value into a duration value.

[PhineasT]

Thank you for your continued assistance.

I fiddled around with it some more this morning. This is what I have now; While being unsure of all of the 'best practices' and such.

Please, let me know, if you would, if anything stands out to you as potentially troublesome.

Default View -

View with TicketNumber 'S16' chosen in the table visual -

[lbendlin]

- the slicers could move to the filter pane

- the day cards could benefit from a small multiples approach

- the bottom left two charts don't carry a lot of information and should be replaced with something more insightful.