cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
st-mb Frequent Visitor
Frequent Visitor

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):

 

image.png

 

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Comparing Start/Stop Times

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 7
Super User
Super User

Re: Comparing Start/Stop Times

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 Frequent Visitor
Frequent Visitor

Re: Comparing Start/Stop Times

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:

 

image.png

 

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

 

Thanks again!

 

 

Super User
Super User

Re: Comparing Start/Stop Times

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 Frequent Visitor
Frequent Visitor

Re: Comparing Start/Stop Times

Hello @Zubair_Muhammad

 

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

 

Thanks!

Super User
Super User

Re: Comparing Start/Stop Times

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 )

 

 

Super User
Super User

Re: Comparing Start/Stop Times

@st-mb

 

start stop.png

st-mb Frequent Visitor
Frequent Visitor

Re: Comparing Start/Stop Times

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:

 

 

image.png

 

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!

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 260 members 3,128 guests
Please welcome our newest community members: