cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
Employee

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors