Duration or Working Hours excluding Holidays

[Anonymous]

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

[AilleryO]

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

[Anonymous]

Thank you @AilleryO 

Yes our DimDate table has IsHoliday column.

[Anonymous]

I'm looking for number of Working Hours.

[v-yiruan-msft]

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...

1. Create one calculated column under table DateDim to get the current date is working day or non-working day
2. Create one calculated column to calculate working hours  

Best Regards

Rena

[Anonymous]

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.

[v-yiruan-msft · Accepted 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

[Anonymous]

Thank you, Rena. This is exactly what I need.

Thanks again,
Kumar