The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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?
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.
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 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 ,
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 @RajP ,
Can you share the data in a file please? Typing data in Power BI is cumbersome.
Thanks,
Pragati
ok, will be with you shortly
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?
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
99 | |
81 | |
62 | |
54 |
User | Count |
---|---|
249 | |
119 | |
115 | |
94 | |
70 |