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 t...
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 )

     

     

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

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

    Hello Zubair_Muhammad

     

    I sent you a PM with the dataset and pbix file.

     

    Thanks!

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      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 )