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 )

     

     

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 )

     

     

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

      Hello Zubair_Muhammad

       

      Thank you for that revised measure, it seems to be closer to what I need.

       

      I'm using Excel to validate the numbers and all of the numbers match up except for one agent. Excel counts 31 2nd interactions, while Power BI counts 32. I've narrowed it down to this one miscount:

       

       

       

      I'm unsure if this is an error with your measure, as all the other agent numbers are correct. Perhaps it's some kind of bug within Power BI?

       

      Thanks!

       

      edit:

       

      I just saw that your measure compares to all previous chats, so this particular agent had a single long chat then two short ones, which is why it counted twice.

       

      With this taken into consideration, everything appears to be correct! Thank you very much @Zubair_Muhammad!