Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vtechz
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

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
Resident Rockstar
Resident Rockstar

@vtechz,

 

Please do check this function COUNTX()

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

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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.