Creating DAX rules for calculating time difference between received and sent dates of a ticket

[Jack_Reacher]

I want to create Dax rules to display two dates when the ID_Reciever received the ticket_ID (based on the ID_Receiver column), and simultaneously be able to display the date when the ID_assistant sent the ticket_ID (based on ID_Assistant column), then I want another separate DAX code to calculate time difference between the two dates.

For example, Received ID SD00316 (Kobbe Bryan), received ticket (DOSSIER_Nb) 15729697 on 21/02/2023, then his same "assistant ID" SD00316, sent the ticket 15729697 on 23/02/2023, the time in days it took Kobbe Bryan to treat this ticket is 2 days. How can I translate this into DAX functions please.

This is based on the table below:

DOSSIER_Nb Date_Action Name Assistant ID_Assistant ID_Receiver Name Receiver
15729697 21/02/2023 Jennifer Johnson E580333  SD00316 Kobbe Bryan
15729697 23/02/2023 Kobbe Bryan SD00316 E580333 Jennifer Johnson
15729697 14/03/2023 Jennifer Johnson E580333 SD00316 Kobbe Bryan
15729697 21/03/2023 Kobbe Bryan SD00316 E580333 Jennifer Johnson

I tried the following three DAX codes, but nothing seems to work: 

Received Date = CALCULATE ( MAX ( 'WEEKLY_IDs'[Date_Action] ), FILTER ( 'WEEKLY_IDs', 'WEEKLY_IDs'[DOSSIER_Nb] = 'WEEKLY_IDs'[DOSSIER_Nb] && 'WEEKLY_IDs'[ID_Receiver] = 'WEEKLY_IDs'[ID_Receiver] ) )

Sent Date = CALCULATE ( MAX ( 'WEEKLY_IDs'[Date_Action] ), FILTER ( 'WEEKLY_IDs', 'WEEKLY_IDs'[DOSSIER_Nb] = 'WEEKLY_IDs'[DOSSIER_Nb] && 'WEEKLY_IDs'[ID_Assistant] = 'WEEKLY_IDs'[ID_Assistant] ) )

DaysBetween =

VAR current_ticket_id = MAX(WEEKLY_IDs[DOSSIER_Nb])
VAR current_recipient_id = MAX(WEEKLY_IDs[ID_Receiver])
VAR current_assistant_id = MAX(WEEKLY_IDs[ID_Assistant])
VAR received_date = CALCULATE(                      
MAX(WEEKLY_IDs[Date_Action]),        
             
FILTER(                        
WEEKLY_IDs,                        
WEEKLY_IDs[DOSSIER_Nb] = current_ticket_id &&                        
WEEKLY_IDs[ID_Receiver] = current_recipient_id                       )                     )

VAR sent_date = CALCULATE(                  
MAX(WEEKLY_IDs[Date_Action]),                  
FILTER(                    
WEEKLY_IDs,                    
WEEKLY_IDs[DOSSIER_Nb] = current_ticket_id &&                    
WEEKLY_IDs[ID_Assistant] = current_assistant_id                   )                 )
RETURN DATEDIFF(sent_date, received_date, DAY)

[P_d2023 · Accepted solution]

Hi, it is tricky as you dont have anything to show when it is a request or a reply

i have got through this by first creating 2 custom columns

column 1 ticket timeline. this now ranks the ticket lines in order of date

secondly i created a new custom column that that ranks the assistant name by date filtered by the ticket

with the 2 columns we can now create measures to calculate what you are looking for

Measure one is ticket_first_recieved_date

this gives us the first time the ticket existed

second measure is first time someone replied that is different than the first sender

once we have the 2 dates we can now calculate the days between in a measure

now we have all the information we can put this into a table as below

hope this helps

please tick if this is the solution to your issue

regards

[P_d2023 · Accepted solution]

Hi, it is tricky as you dont have anything to show when it is a request or a reply

i have got through this by first creating 2 custom columns

column 1 ticket timeline. this now ranks the ticket lines in order of date

secondly i created a new custom column that that ranks the assistant name by date filtered by the ticket

with the 2 columns we can now create measures to calculate what you are looking for

Measure one is ticket_first_recieved_date

this gives us the first time the ticket existed

second measure is first time someone replied that is different than the first sender

once we have the 2 dates we can now calculate the days between in a measure

now we have all the information we can put this into a table as below

hope this helps

please tick if this is the solution to your issue

regards

[Jack_Reacher]

Kudos to you, BIG Thank you, your answers helped resolve this problem, I just have one question, for the tickets that treated on the same days, it's natural that they don't have a reply date, when I applied the last measure, I get a significant negative number that I just want to show as zero, how can I accomplish this please?

[P_d2023 · Accepted solution]

Hi Jack you can add an IF stament to the days between calc as below

this should now show any days that are less than zero as zero

regards