TAT Calculation based on Next Row

[rocky09]

Here is my situation, I need to find TAT for a Ticket.  A Ticket may contains many activities. Basically, the activities types will be Outbound and Inbound. 

Requirement:

* Calculate TAT only when the first Row Has Outbound and next Row has Inbound.

* If only One Inbound/One outbound seen, just ignore calculating tat.

* TAT also should based on Ticket ID.

Please see below, so, you will get a clear picture of what I am after.

254520	10/2/2017 11:03	Outbound
254520	10/2/2017 0:33	Inbound
254520	10/2/2017 18:40	Outbound
254520	10/2/2017 18:58	Inbound
254520	10/2/2017 19:58	Outbound
254525	10/3/2017 10:03	Inbound
254525	10/3/2017 10:48	Outbound
254525	10/4/2017 14:06	Inbound

Expecting solution

ID	Date	Type	TAT
254520	10/2/2017 11:03	Outbound	0.020833
254520	10/2/2017 11:33	Inbound
254520	10/2/2017 18:40	Outbound	0.0125
254520	10/2/2017 18:58	Inbound
254520	10/2/2017 19:58	Outbound
254525	10/3/2017 10:03	Inbound
254525	10/3/2017 10:48	Outbound	1.1375
254525	10/4/2017 14:06	Inbound

see above Bold, there is outbound and inbound but the ticket ids are not matching. So, we can skip to calculate TAT.

[MarcelBeug · Accepted solution]

It looks like TAT stands for Turn Around Time, at least the following Power Query solution returns the same results as your examples.

By adding a base-0 index and a base-1 index, you can merge the table with itself so you get the data from the next row on the current row.

let
    Source = Tickets,
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index.1"},#"Added Index1",{"Index"},"Next",JoinKind.LeftOuter),
    #"Expanded Next" = Table.ExpandTableColumn(#"Merged Queries", "Next", {"ID", "Date", "Type"}, {"Next.ID", "Next.Date", "Next.Type"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Next", "TAT", each if [ID] = [Next.ID] and [Type] = "Outbound" and [Next.Type] = "Inbound" then [Next.Date] - [Date] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Index.1", "Next.ID", "Next.Date", "Next.Type"})
in
    #"Removed Columns"

[Ashish_Mathur · Accepted solution]

Hi,

Try this calculated column formula

=if(AND(LOOKUPVALUE([Type],[Date],CALCULATE(MIN(Data[Date]),FILTER(Data,[ID]=EARLIER([ID])&&Data[Date]>EARLIER(Data[Date]))))="inbound",[Type]="outbound"),CALCULATE(MIN(Data[Date]),FILTER(Data,[ID]=EARLIER([ID])&&Data[Date]>EARLIER(Data[Date])))-Data[Date],BLANK())

Download the workbook from here.

[rocky09]

Thank you @Ashish_Mathur and @MarcelBeug

[Ashish_Mathur]

You are welcome.

[MarcelBeug · Accepted solution]

It looks like TAT stands for Turn Around Time, at least the following Power Query solution returns the same results as your examples.

By adding a base-0 index and a base-1 index, you can merge the table with itself so you get the data from the next row on the current row.

let
    Source = Tickets,
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index.1"},#"Added Index1",{"Index"},"Next",JoinKind.LeftOuter),
    #"Expanded Next" = Table.ExpandTableColumn(#"Merged Queries", "Next", {"ID", "Date", "Type"}, {"Next.ID", "Next.Date", "Next.Type"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Next", "TAT", each if [ID] = [Next.ID] and [Type] = "Outbound" and [Next.Type] = "Inbound" then [Next.Date] - [Date] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Index.1", "Next.ID", "Next.Date", "Next.Type"})
in
    #"Removed Columns"