Forum Discussion
Calculate Age between Duplicates
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)
Super UserOne 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)
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:
Found a solution to this last part using "Fill Up", and then removed duplicates as they were no longer needed.