Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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

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 sen...
  • P_d2023's avatar
    P_d2023
    3 years ago

    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's avatar
    P_d2023
    3 years ago

    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

     

P_d2023's avatar
P_d2023
Regular Visitor
3 years ago

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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      P_d2023
      Regular Visitor
      3 years ago

      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