cancel
Showing results for
Did you mean:
Frequent Visitor

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

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.

1 ACCEPTED SOLUTION
Frequent Visitor

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"
)
))
4 REPLIES 4
Resident Rockstar

Please do check this function COUNTX()

Frequent Visitor

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
Microsoft

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

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"
)
))

Announcements

#### Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors