Forum Discussion

Tucker1991's avatar
Tucker1991
Frequent Visitor
8 years ago
Solved

Difference Between Consecutive Rows - When Date is The Same

Hi,

 

So theoretically I know exactly what I want to do. I am simply having a difficult time executing that in Power Bi.

 

Basically I have a ton of data on helpdesk tickets. 

 

What I want is the difference in closure times for conescutive tickets per agent per day. 

 

I.E. How long did Agent A spend between closing ticket 1 versus ticket 2 versus ticket 3 versus ticket 4 on Septemeber 12th ect... ect...

 

 

 

The actual data has about 20 agents and over 75,000 tickets.

  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi Tucker1991,

     

    Please create some calculated columns with below formulas:

    Index =
CALCULATE (
    COUNTROWS ( 'Agent ticket' ),
    FILTER (
        ALL ( 'Agent ticket' ),
        'Agent ticket'[Agent] = EARLIER ( 'Agent ticket'[Agent] )
            && 'Agent ticket'[Date Closed].[Date]
                = EARLIER ( 'Agent ticket'[Date Closed].[Date] )
            && 'Agent ticket'[Ticket ID] < EARLIER ( 'Agent ticket'[Ticket ID] )
    )
)
    + 1

Closed time for previous Ticket =
CALCULATE (
    MAX ( 'Agent ticket'[Date Closed] ),
    FILTER (
        ALLEXCEPT (
            'Agent ticket',
            'Agent ticket'[Agent],
            'Agent ticket'[Date Closed].[Date]
        ),
        'Agent ticket'[Index]
            = EARLIER ( 'Agent ticket'[Index] ) - 1
    )
)

spend time =
DATEDIFF (
    'Agent ticket'[Closed time for previous Ticket],
    'Agent ticket'[Date Closed],
    SECOND
)

     

    Best regards,

    Yuliana Gu

2 Replies

  • Tucker1991's avatar
    Tucker1991
    Frequent Visitor
    8 years ago

    Would I basically want to use a DAX formula similar to the one below.

     

    Column = DATEDIFF (
    'Ticket Data'[Time],
    CALCULATE (
    MIN ( [Time] ),
    FILTER ( ALL ( 'Ticket Data' ), [INDEX] < EARLIER ( 'Ticket Data'[INDEX] ) )
    ),
    SECOND
    )

     

    But  add IF Agent and Date is the same?

     

    Thanks,

     

     

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    Hi Tucker1991,

     

    Please create some calculated columns with below formulas:

    Index =
CALCULATE (
    COUNTROWS ( 'Agent ticket' ),
    FILTER (
        ALL ( 'Agent ticket' ),
        'Agent ticket'[Agent] = EARLIER ( 'Agent ticket'[Agent] )
            && 'Agent ticket'[Date Closed].[Date]
                = EARLIER ( 'Agent ticket'[Date Closed].[Date] )
            && 'Agent ticket'[Ticket ID] < EARLIER ( 'Agent ticket'[Ticket ID] )
    )
)
    + 1

Closed time for previous Ticket =
CALCULATE (
    MAX ( 'Agent ticket'[Date Closed] ),
    FILTER (
        ALLEXCEPT (
            'Agent ticket',
            'Agent ticket'[Agent],
            'Agent ticket'[Date Closed].[Date]
        ),
        'Agent ticket'[Index]
            = EARLIER ( 'Agent ticket'[Index] ) - 1
    )
)

spend time =
DATEDIFF (
    'Agent ticket'[Closed time for previous Ticket],
    'Agent ticket'[Date Closed],
    SECOND
)

     

    Best regards,

    Yuliana Gu