Difference Between Consecutive Rows - When Date is The Same

[Tucker1991]

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 · Accepted solution]

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

[Tucker1991]

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,