topic Re: Creating DAX rules for calculating time difference between received and sent dates of a ticket in DAX Commands and Tips

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

Anonymous — Mon, 10 Apr 2023 00:55:55 GMT

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)

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

P_d2023 — Mon, 10 Apr 2023 11:52:18 GMT

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

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

Anonymous — Mon, 10 Apr 2023 14:47:19 GMT

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?

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

P_d2023 — Mon, 10 Apr 2023 14:55:40 GMT

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