Track missed message with timestamp

[Trebor84]

Hi,

Hoping someone can assist please.  Our team deal with incoming messages from suppliers and when responding the message is stamped with the date/time of the response.

I am trying to find a way either with Power Query or DAX to locate where the oldest outstanding message wasn't dealt with first.

In the below example we have incoming messages on 3 job ID's (unique identifier).  As you can see from this Chris B read a message on Job ID J12346 at 02/01/2024 07:25:45 but didn't pick up the oldest message which was originally received on Job ID J12345 at 01/01/2024 23:59:46.  Simmilarly, Jenny C answered a message at 02/01/2024 07:20:45 on job ID J12347 but didn't respond to Job ID's J12345 or J12346 as they were the oldest unread messages in the queue at that point.

What I ideally want to do is show this as a count to show that both Chris B and Jenny C didn't answer the oldest message then display in a table the message they handled as well as the message they should have answered first?

Jobs can have multiple messages and the aim is that the oldest message in the queue should always be answered first.  

Date/Time Sent	Job ID	In/Out	Sender	Reader	Message	Read Date/Time Stamp
01/01/2024 23:59	J12345	In	Company A	Alex G	Please can you help me with a query?	02/01/2024 07:30
02/01/2024 00:05	J12346	In	Company B	Chris B	Sorry we are running late	02/01/2024 07:25
02/01/2024 07:10	J12347	In	Company C	Jenny C	Please call me urgently	02/01/2024 07:20

Hope this makes sense.

[dufoq3]

Hi @Trebor84, 

1.) how do we know which message has reader answered?
2.) could you provide expected result based on sample data?

[Trebor84]

You wouldn't know when a message has been answered as not all inbound messages require a response.

The reader is expected to always go into the oldest incoming message first which then timestamps it.  What I'm trying to ascertain from this exercise is for each message, who isn't dealing with it, the job id of the message they dealt with instead and the count of missed oppertunities for that message.

I have amended my sample data to show how outbound responses are captured but like I say, for the purpose of this I'm only interested in outbound messages where the oldest message isn't being read first.  Please also note that for any outbound messages we don't capture the read date/time of when our external supplier reads the response from their side.

Date/Time Sent	Job ID	In/Out	Sender	Reader	Message	Read Date/Time Stamp
01/01/2024 23:59	J12345	In	Company A	Alex G	Please can you help me with a query?	02/01/2024 07:30
02/01/2024 00:05	J12346	In	Company B	Chris B	Sorry we are running late	02/01/2024 07:25
02/01/2024 07:10	J12347	In	Company C	Jenny C	Please call me urgently	02/01/2024 07:20
02/01/2024 07:30	J12345	Out	Alex G	Company A	How can I help?
02/01/2024 07:25	J12346	Out	Chris B	Company B	Thanks for the update
02/01/2024 07:20	J12347	Out	Jenny C	Company C	Ok will do

Expected output:

Date/Time Sent	Job ID	In/Out	Sender	Reader	Message	Read Date/Time Stamp	Missed By	Missed Opportunity
01/01/2024 23:59	J12345	In	Company A	Alex G	Please can you help me with a query?	02/01/2024 07:30	Reader: Chris B (Job ID: J12346) Reader: Jenny C (Job ID: J12347)	2
02/01/2024 00:05	J12346	In	Company B	Chris B	Sorry we are running late	02/01/2024 07:25	Reader: Jenny C (Job ID: J12347)	1
02/01/2024 07:10	J12347	In	Company C	Jenny C	Please call me urgently	02/01/2024 07:20		0

I have also included a sample in Excel format if this helps.

https://file.io/Aw3SXLSVOaae 

[Trebor84]

You wouldn't know if a reader has answered a message as some messages don't require a response. Every reader should always go into the oldest message first which then timestamps the message with a read date/time. What I'm trying to accomplish is to find who isn't dealing with the oldest message in the queue first then for that message count how many times it was missed, who missed it, and what job the invividual dealt with instead.

I've updated my example to show how responses are recorded, but like I say some messages won't have a response but someone is still expected to "read" it to get the message out of the queue.  I'm only interested in the messages "In" for this excercise.  For info, outbound messages don't have a read date/time stamp as we don't track when our external suppliers read our responses.

Date/Time Sent	Job ID	In/Out	Sender	Reader	Message	Read Date/Time Stamp
01/01/2024 23:59	J12345	In	Company A	Alex G	Please can you help me with a query?	02/01/2024 07:30
02/01/2024 00:05	J12346	In	Company B	Chris B	Sorry we are running late	02/01/2024 07:25
02/01/2024 07:10	J12347	In	Company C	Jenny C	Please call me urgently	02/01/2024 07:20
02/01/2024 07:30	J12345	Out	Alex G	Company A	How can I help?
02/01/2024 07:25	J12346	Out	Chris B	Company B	Thanks for the update
02/01/2024 07:20	J12347	Out	Jenny C	Company C	Ok will do

Expected output:

Date/Time Sent	Job ID	In/Out	Sender	Reader	Message	Read Date/Time Stamp	Missed By	Missed Opportunity
01/01/2024 23:59	J12345	In	Company A	Alex G	Please can you help me with a query?	02/01/2024 07:30	Reader: Chris B (Job ID: J12346) Reader: Jenny C (Job ID: J12347)	2
02/01/2024 00:05	J12346	In	Company B	Chris B	Sorry we are running late	02/01/2024 07:25	Reader: Jenny C (Job ID: J12347)	1
02/01/2024 07:10	J12347	In	Company C	Jenny C	Please call me urgently	02/01/2024 07:20		0