Identify if a suport ticket is followed up consistently

[Kiranganesh]

Helllo, 

I have a table with 3 columns:

Support Ticket Number	Status	Date
3789	In	31-07-2020 14:53
6490	Out	30-07-2020 12:45
3789	In	28-07-2020 08:51
3789	Out	29-07-2020 11:53
6490	Out	29-07-2020 10:30

I would want to check how many times a support ticket (for example) "3789"with status "In" as missed an entry in the table between the first entry for the Support Ticket and the last entry in the table. Note: The status shoule be "In" or "Out" on daily basis, if the status is "In" but as missed to follow-up on the next day, then the support ticket needs to be flagged.

Can someone help on this this ?. hope the scenario mentioned above is understandable.

Regards,

Kiran

[nandic]

@Kiranganesh , could you share some more information?
So there are 2 requirements:
1) add a flag if status was yesterday "In" and today there is no info (there was no follow up). What if today status is "Out"?
Another question, does it always compare to next day or next appearance in table? (Ie: if it is weekend, is it still expected to be followed up or not?)
2) count if status "In". Count how many times each support ticket had status "In", right?
So you will get table like this:
Ticket   In-counter
3789     2
6490     0
Or it has different logic?

Best way is to add needed column (make it in excel) and share expected results.

[amitchandak]

@Kiranganesh , a measure like this will help you

But have a date only column

Date New = [Date].date

countx(filter(summarize(table, table[Support Ticket],Table[Date New],"_1",calculate(count(table[Support Ticket]),Table[Status]="In"),"_2",calculate(count(table[Support Ticket]),Table[Status]="Out")),[_1]<[_2]),[Support Ticket])

[Kiranganesh]

Thanks for replying. Unfortunately, the proposed solution did not work. May be my request was not put up clearly, let me attempt again:

Tickets are raised by users, once the ticket is recieved the status of the ticket changes to "In", if the request is responded by suppot team(Seeking additional clarification on the reported issue) the status of the ticket changes to "Out". There could be mulltip "In" and "Out" for a single ticket.

My inrest is to find how many of the tickets were followed up(in days- ">1") by either "users" or "support team" without the other responding. Also, include the count of in between days the follow-up or response was missed by either "user" or "support team".

Data from a table:

Support Ticket Number	Status	Date
229156	In	01 January 2020
229156	Out	06 January 2020
229158	In	01 January 2020
229158	Out	01 January 2020
229176	In	02 January 2020
229176	In	03 January 2020
229176	In	06 January 2020
229176	In	22 January 2020
229185	In	01 January 2020
229185	In	02 January 2020
229185	In	03 January 2020
229185	In	06 January 2020
229185	Out	06 January 2020

Expected Result:

Support Ticket Number	Status	Date	Missed_Days
229156	In	01 January 2020	5
229156	Out	06 January 2020	5
229176	In	06 January 2020	3
229176	In	22 January 2020	16
229185	In	06 January 2020	3

[harshnathani]

Hi @Kiranganesh 

Create a Calculated Column

Column = 
VAR __thislogin = 'Table'[Date]
VAR __nextlogout =
CALCULATE(
MIN ( 'Table'[Date] ),
ALLEXCEPT ( 'Table', 'Table'[Support Ticket Number] ),
'Table'[Date] > __thislogin

)

RETURN
IF (
'Table'[Status] = "IN",
DATEDIFF ( __thislogin, __nextlogout, DAY ),
BLANK ()
)

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)