Elapsed Time

[RajP]

Hi,

I'm fairly new to Power BI, and i'm trying to calculate the elapsed time between when the call comes in to the current time / time responded, taking into account business hours and weekends. 

For example, call comes in today at 11am and at 3.30pm the same day the elapse time would be 4 hours 30 minutes, but if a call comes in at 4pm on Friday and is responded 10am on Monday morning, the elaspe time would be 2 hours, based on a 9-5 day. 

Is anyone able to help please?

[v-yangliu-msft]

Hi  @RajP  ,

If you want to get now, you can directly change the time column to this form:

TIME =
IF(
    [Desp. Da]=BLANK(),BLANK(),
IF(
WEEKDAY([Call Date])=4&&WEEKDAY([Take Date])=7&&WEEKNUM([Call Date])=WEEKNUM([Take Date]),TIME(2,0,0),NOW()-[Call Time]))

Compare the calculated time with your index

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yangliu-msft]

Hi  @RajP  ,

Here are the steps you can follow：

1. Create calculated column.

Take Time =
IF(
[Fin. Dat]=BLANK(),IF([Arrival]=BLANK(),[Desp.],[Arr.]),[Fin.])

Take Time =
IF(
[Fin. Dat]=BLANK(),IF([Arrival]=BLANK(),[Desp.],[Arr.]),[Fin.])

TIME =
IF(
    [Desp. Da]=BLANK(),BLANK(),
IF(
WEEKDAY([Call Date])=4&&WEEKDAY([Take Date])=7&&WEEKNUM([Call Date])=WEEKNUM([Take Date]),TIME(2,0,0),[Take Time]-[Call Time]))

2. Result.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RajP]

Thank you Liu Yang, unfortunately this doesn't resolve my issue. I need to compare Call Date and Time with 'Now()' time, so I can determine where we haven't met SLA, taking into account business hours.

[Anonymous]

Hi Pragati, I am a friend of this Power BI User, please use this link:-

https://drive.google.com/file/d/1SQzEKE1LRqUxrHhvly_6Gh5MOqpkffOC/view?usp=sharing 

[Pragati11]

Hi @RajP ,

Please share some sample data in a file and the required output based on this sample data.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Thanks,

Pragati

[RajP]

Hi Pragati,

Thank you for coming back to me. Please see sample data below:

Hopefully you can see the data, but basically it shows the call date and call time, and i need to identify any calls that have not been responded to within 4 working hours. I do have a businesshour table that shows Start of the day (9am) and End (5pm), along with a calendar table as below:

Please let me know if you require any more info?

[Pragati11]

Hi @RajP ,

Can you share the data in a file please? Typing data in Power BI is cumbersome.

Thanks,

Pragati

[RajP]

ok, will be with you shortly

[RajP]

Sorry Pragati, I'm struggling to attach the file, I've tried attaching an Excel sheet, CSV and the power BI file and it says that the file type is not supported, any ideas?