Opened and Closed ticket count by Shift along with Carry-Over count

[vtechz]

Hi Team,

I am very new to Power BI and I was using Tableau. I have a data which will have Ticket Number, Created Date, Resolved Date and Status.

Requirement is to group the tickets created count by Shift. (Three shifts 6-2, 2-22, 22-6) --> I have achieved this by using the Link

But then I have to count the number of tickets closed on the particular shift and number of tickets carry-over for the next shift. I have no clue on how to proceed further.

Please help

[SivaMani]

@vtechz,

Please do check this function COUNTX()

[vtechz]

Thanks for quick response. My data and requirement is like below. I achieved the first two columns but no clue for the 2nd and 3rd. If you look at the 2nd column. In the 2nd column for Shift 2 we have to tell how many tickets closed out of 10 which was opened in Shift 2. But during shift 2 9 was closed totally. Also the carryover has to include the remaining 5.

Result:

	Opened	Closed During the Shift	Carryover to Next Shift
Shift 1	6	1	5
Shift 2	10	5 (9 Closed but 5 is Shift 2)	5
Shift 3	4	0	4

Data:

ID	Created On	Resolved On	Status
2	31/01/2018 07:10		Open
3	31/01/2018 07:10	31/01/2018 17:01	Closed
7	31/01/2018 07:10	31/01/2018 17:01	Closed
8	31/01/2018 07:10	31/01/2018 17:01	Closed
9	31/01/2018 07:10	31/01/2018 17:01	Closed
12	31/01/2018 07:10	31/01/2018 07:10	Closed
15	31/01/2018 16:23	31/01/2018 16:26	Closed
33	31/01/2018 17:32		Open
34	31/01/2018 17:40	11/05/2018 18:48	Closed
36	31/01/2018 17:45	31/01/2018 17:50	Closed
37	31/01/2018 17:53	31/01/2018 17:55	Closed
38	31/01/2018 17:59	31/01/2018 18:04	Closed
39	31/01/2018 18:13		Open
40	31/01/2018 18:45		Open
41	31/01/2018 18:45		Open
42	31/01/2018 19:01		Open
50	31/01/2018 23:30		Open
51	31/01/2018 23:30		Open
52	31/01/2018 23:30		Open
59	31/01/2018 23:30		Open

[v-cherch-msft]

Hi @vtechz

I cannot fully understand the Closed During the Shift and Carryover to Next Shift columns.Could you explain more about your expected output?How to get these two columns?

Regards,

Cherie

[vtechz · Accepted solution]

Finally managed to find the solution.. I used the below for Created Shift and Closed Shift slots. Then calculated the count of tickets based on Shift value.

Created Shift:

Shift =

IF (

    Incidents[Opened Time]>= TIME ( 6, 0, 0 )

        && Incidents[Opened Time] < TIME ( 14, 0, 0 ),

    "Shift-1",

    IF (

        Incidents[Opened Time]>= TIME ( 14, 0, 0 )

            && Incidents[Opened Time] < TIME ( 22, 0, 0 ),

        "Shift-2",

        "Shift-3"

    )

)

Resolved Shift:

Resolved Shift =

IF (

    Incidents[Resolved Time] >= TIME ( 6, 0, 0 )

        && Incidents[Resolved Time] < TIME ( 14, 0, 0 ),

    "Shift-1",

    IF (

        Incidents[Resolved Time] >= TIME ( 14, 0, 0 )

            && Incidents[Resolved Time] < TIME ( 22, 0, 0 ),

        "Shift-2",

        IF (Incidents[Resolved Time] = 0,"",

        "Shift-3"

    )

))