Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Calculate Age between Duplicates

I have a table with ticket data where each ticket appears twice - one with the oldest date/time an event occurred, and one with the newest date/time an event occurred. They are labelled in a column a...
Need Help
  • vicky_'s avatar
    vicky_
    3 years ago

    One way of doing it would be to change the data structure in power query using the pivot functions. 

    So you would have something like 
    Ticket | Incoming Time | Outgoing time | (calculated) time difference.

     

    If you need to keep the data the same, then you could create a calculated column like:
    Age = 

    var outgoingTime = CALCULATE(MAX([Timestamp]), FILTER(ALL([TableName]), [TICKET_ID] = EARLIER([TICKET_ID]) && [DIRECTION] = "Outgoing")

    return DATEDIFF(outgoingTime, [TIMESTAMP], DAY)

    (note that this functio returns a whole number representing the whole days between the two dates)

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Vicky,

Thanks for your reply.

 

I think you are right, I need to use Pivot to get a single row with both timestamps as you described.

 

I am trying to do that, but when I pivot, I still have two rows per Ticket ID. I now have two new columns of each DIRECTION, Incoming and Outgoing, so I think i'm on the right track. The question now is how can I get the value from every second row's Outgoing column to fill the null in the row above it?

Example of one "pair" of a single ticket:

 

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Found a solution to this last part using "Fill Up", and then removed duplicates as they were no longer needed.