Forum Discussion
Elapsed Time
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?
9 Replies
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
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?
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
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.
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.
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.
