Forum Discussion
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...
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
Community Champion
Community ChampionHi 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 )
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
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
