cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
vtechz Frequent Visitor
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.

 

Please help

1 ACCEPTED SOLUTION

Accepted Solutions
vtechz Frequent Visitor
Frequent Visitor

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

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

View solution in original post

4 REPLIES 4
SivaMani Senior Member
Senior Member

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

@vtechz,

 

Please do check this function COUNTX()

vtechz Frequent Visitor
Frequent Visitor

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

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:

 

 OpenedClosed During the ShiftCarryover to Next Shift
Shift 1615
Shift 2105 (9 Closed but 5 is Shift 2)5
Shift 3404

 

Data:

 

IDCreated OnResolved OnStatus
231/01/2018 07:10 Open
331/01/2018 07:1031/01/2018 17:01Closed
731/01/2018 07:1031/01/2018 17:01Closed
831/01/2018 07:1031/01/2018 17:01Closed
931/01/2018 07:1031/01/2018 17:01Closed
1231/01/2018 07:1031/01/2018 07:10Closed
1531/01/2018 16:2331/01/2018 16:26Closed
3331/01/2018 17:32 Open
3431/01/2018 17:4011/05/2018 18:48Closed
3631/01/2018 17:4531/01/2018 17:50Closed
3731/01/2018 17:5331/01/2018 17:55Closed
3831/01/2018 17:5931/01/2018 18:04Closed
3931/01/2018 18:13 Open
4031/01/2018 18:45 Open
4131/01/2018 18:45 Open
4231/01/2018 19:01 Open
5031/01/2018 23:30 Open
5131/01/2018 23:30 Open
5231/01/2018 23:30 Open
5931/01/2018 23:30 Open
v-cherch-msft Super Contributor
Super Contributor

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

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.
vtechz Frequent Visitor
Frequent Visitor

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

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

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 182 members 2,283 guests
Please welcome our newest community members: