Forum Discussion

st-mb's avatar
st-mb
Frequent Visitor
8 years ago
Solved

Comparing Start/Stop Times

Hello,

 

I was hoping to use Power BI to make a report that counts the number of times where a service agent is on two interactions at once (for any sliced date range), but I'm not quite sure how to proceed.

 

The data source has multiple agents, and I was thinking that there would need to be some kind of comparison between the "Arrival" time and the "Chat Finished" time of the previous row (while taking into account the "Date" and "Name" so that data is not compared against another agent or date). Here an image of some of the data (I've highlighted times where two interactions occured at the same time):

 

 

I was thinking it would be easier to perform the comparison if the "Date" was merged into "Arrival" and "Chat Finished" and converted to a Date/Time, but I'll defer to the experts.

 

Thanks in advance!

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi st-mb

     

    Please try this revised MEASURE

     

    Spot 2nd Interaction =
VAR PreviousChat =
    CALCULATE (
        MAX ( December[Chat Finished] ),
        FILTER (
            ALLEXCEPT ( December, December[Name], December[Date] ),
            december[Arrival] < EARLIER ( december[Arrival] )
        )
    )
VAR PreviousArrival =
    CALCULATE (
        MAX ( December[Arrival] ),
        FILTER (
            ALLEXCEPT ( December, December[Name], December[Date] ),
            December[Arrival] < EARLIER ( December[Arrival] )
        )
    )
RETURN
    IF ( December[Arrival] < PreviousChat, 1 )

     

     

7 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    Hi st-mb

     

    Try this calculated Column

     

    Intention is to assign a value 1 to the row where the  current row arrival time is less than previous chat finished time for the same AGENT and same DAY

    Then we can easily count them

     

    Spot 2nd Interaction =
VAR PreviousChat =
    CALCULATE (
        MAX ( TableName[Chat Finished] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Name], TableName[Date] ),
            TableName[Chat Finished] < EARLIER ( TableName[Chat Finished] )
        )
    )
VAR PreviousArrival =
    CALCULATE (
        MAX ( TableName[Arrival] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Name], TableName[Date] ),
            TableName[Arrival] < EARLIER ( TableName[Arrival] )
        )
    )
RETURN
    IF ( TableName[Arrival] < PreviousChat, 1 )

     

    • st-mb's avatar
      st-mb
      Frequent Visitor
      8 years ago

      This is looking great, Zubair_Muhammad!

       

      There seems to be a few instances where a second interaction is not captured for some reason. Such as this:

       

       

      Any idea why this may be occurring? It looks like this particular agent should have had 4 2nd interactions.

       

      Thanks again!

       

       

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        Hi st-mb

         

        Could you share your file?

         

        With the formula I gave, the 2 rows with Orange color should be spotted

         

        i..e. rows with arrival time< previous chat finished