Ticket Open For more than 20 days

[ashaikh]

Hello,

I am creating a Weekly Report in which I want to have a line charts which provides count of ticket open for more than 20 days. The line chart will have last 10 weeks on the X-axis and count of open tickets on Y-axis.

This is how the data looks like:

ID	START TIME	END TIME	STATUS
380	9/19/17 22:34	NULL	Pending
1233	10/6/17 17:41	NULL	Pending
1301	10/9/17 13:12	NULL	Pending
1613	10/16/17 13:43	NULL	Pending
1628	10/16/17 17:16	NULL	Pending
1652	10/17/17 12:35	NULL	In Progress
1680	10/17/17 15:19	NULL	Pending
1970	10/24/17 9:59	NULL	In Progress
2011	10/24/17 15:38	NULL	In Progress
2012	10/24/17 16:11	NULL	Pending
2029	10/25/17 9:29	NULL	In Progress
2179	10/27/17 14:22	NULL	Pending
2230	10/30/17 12:19	NULL	In Progress
2340	11/1/17 8:56	NULL	In Progress
2345	11/1/17 10:55	NULL	In Progress
2381	11/1/17 13:52	NULL	In Progress
2401	11/2/17 10:05	NULL	In Progress
2454	11/2/17 17:33	NULL	In Progress
2467	11/3/17 10:40	NULL	In Progress
2470	11/3/17 10:42	NULL	In Progress

Now, here how the report should look like is suppose I am creating a report for this week so suppose the count of ticket open is 8 as shown in table below. Then in the graph report should show coresponding count 8

DATA

DURATION	COUNT
12/11/17 - 18/12/17	12
19/11/17 - 25/11/17	8

LINE CHART

  

Now, when I create the report for next week i.e. 11/26/17 - 12/2/17 even if the there might be any tickets which showed open for more than 20 days are resolved the count in the last week should remain same.

For example: From above line chart there were 8 tickets which were open for more than 20 days. Now in the next week out of 8, 3 tickets were resolved so last week count should still remain same i.e 8 and 5 tickets which are open should move to the 11/26/17- 12/2/17 report.

It will be great if someone can help me with how can I implemen this.

Thanks in advance

[v-huizhn-msft]

Hi @ashaikh,

How to judge is a ticket open is open than 20 days, using Start Time and Status? What's the calculation rules? Please share more details for analyzing.

Best Regards,
Angelia

[ashaikh]

Open tickets is by Status. The ticket which are in progress or pending or new,

The duration is calculated by current date - start date.

And yes for all open ticket End Date is blank

[v-huizhn-msft · Accepted solution]

Hi @ashaikh,

First, you need to create a data table including [Duration], then add a custom column(copy of [Duration]), then split it to two columns [Start] and [End] column. The following is my query statement and 'Date' table.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VYzBDcAgDAN34d0qsSktmQWx/xogFSXKz76zPEah4hMT9PskPmVeztl+DhUE343Oe+IW+5YEXjdkvqphLJmqx0D22Vw=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Column1]),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Custom.1", "Custom.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type date}, {"Custom.2", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Column1", "Duration"}, {"Custom.1", "Start"}, {"Custom.2", "End"}})
in
    #"Renamed Columns"

Date table
Then you can create a calculated column using the formula below.

COUNT =
CALCULATE (
    COUNT ( Table1[ID] ),
    FILTER (
        Table1,
        Table1[END TIME] = "NULL"
            && ( Table1[STATUS] = "Pending"
            || Table1[STATUS] = "In Progress"
            || Table1[STATUS] = "New" )
            && Table1[START TIME] >= 'Date'[Start]
            && Table1[START TIME] <= 'Date'[End]
    )
)
    + 0

Finally, you create a table and line chart visualization, you will get expected result as follows.

result

Please download the .pbix file for more details.

Best Regards,
Aneglia