Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, I need help with calculating Duration based on two time stamps
I have
SubmissionDateTime
and
DecisionDateTime
Now business wants to find all the application status with time buckets. How much time/Hours it took to process excluding Holidays/Weekends defined in DateDim table
Ex: If a Customer submitted application on 02/06/2020 at 3:00 PM (Thursday)
and accepted on 02/10/2020 11:00 AM
So Totoal Hours for this application will be:
On Thursday: 3 to 12 = 9 Hours (Thursday EOD 11:59 PM) - Here Clock stops
Friday: 24 Hours
again it should check for next working day before ISNULL(DecisionDate,NOW()) which is Monday 12 AM
On Monday: 12 AM to 11 AM - 11 Hours
Tota: 9 + 24 + 11 = 44 Hours
Reports are based on : < 24 Hours, 24-48 Hours, > 48 Hours.
Note: requirement is to consider 24 hours as Working hours on a Working Day.
it's not just wekkend, Holidays also must be excluded based on pre-defined Company calendar (DateDim table).
Thank you,
Kumar
Solved! Go to Solution.
Hi @Anonymous ,
I created another calculated column "Working hour" to calculate working hours based on your new reply, please find the detailed formula in latest upload file.
Best Regards
Rena
Hi,
You should tell us more about your company calendar, do you have a column saying Working Day Yes/No ?
If so you should be able to use techniques as :
https://powerpivotpro.com/2012/11/networkdays-equivalent-in-powerpivot/
or
https://community.powerbi.com/t5/Desktop/Number-of-working-days/m-p/24157#M7752
Hope it helps,
Oliver
I'm looking for number of Working Hours.
Hi @Anonymous ,
According to the information in your case, I just create one sample PBIX file. Please follow the below steps (just like below screenshot) to get the working hours, you can find the details in shared sample PBIX file...
Best Regards
Rena
Thank you very much, Rena. this is really helpful.
One otherthing i did not mentioned is Customers can submit their applications on Holidays also and in this case below logic is not working.
Ex: if Submit Date is Holiday, then we start Duration from next working Day.
Hi @Anonymous ,
I created another calculated column "Working hour" to calculate working hours based on your new reply, please find the detailed formula in latest upload file.
Best Regards
Rena
Thank you, Rena. This is exactly what I need.
Thanks again,
Kumar
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |