Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
st-mb
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

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 )

 

 


Regards
Zubair

Please try my custom visuals

View solution in original post

7 REPLIES 7
Zubair_Muhammad
Community Champion
Community Champion

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 )

 


Regards
Zubair

Please try my custom visuals

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!

 

 

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


Regards
Zubair

Please try my custom visuals

Hello @Zubair_Muhammad

 

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

 

Thanks!

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 )

 

 


Regards
Zubair

Please try my custom visuals

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!

@st-mb

 

start stop.png


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.