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 ...
  • 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

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