Dax for counting minutes between start and end time avoiding weekends and holidays

[christianfcbmx]

Good day community: I have the following chalange: I need to do the same thing I did in excel in power BI!!! It is an advanced dax for counting minutes between start time and end time avoiding weekends and holidays and considering counting hours from 9am to 17pm? I did it in excel but no much clue to do it in power BI..any advice would be really well recieved...Greetings and thanks again for your fabulous channel!!!! excel example that is working pretty good is:(=SI(O($B$2<$A$2;B5<A5);0;(DIAS.LAB(A5;B5;HolidayList)-(DIAS.LAB(A5;A5;HolidayList)*SI(RESIDUO(A5;1)>$B$2;1;(MAX($A$2;RESIDUO(A5;1))-$A$2)/($B$2-$A$2)))-(DIAS.LAB(B5;B5;HolidayList)*SI(RESIDUO(B5;1)<$A$2;1;($B$2-MIN($B$2;RESIDUO(B5;1)))/($B$2-$A$2))))*($B$2-$A$2)*24))

Anybody could give me a hand please!!!!! God bless

[Anonymous · Accepted solution]

Hi @christianfcbmx,

You can simply calculate decimal 'net work hour' in query editor.

Formula:

#"Added Custom" = Table.AddColumn(#"Changed Type1", "WorkHour", each Duration.TotalHours([End Datetime]-[Start Datetime]))

Current power bi not support time format which over 24 hours.
If you need to calculate on this value, you cant convert decimal hours to string format and split it to multiple columns to stored different time unit.

Reference:

Aggregating Duration/Time

Regards,

Xiaoxin Sheng

[christianfcbmx]

Hi @Anonymous... thats definetly could work...but how can I use that in my formula?

Let me clarify what is what I really need:

I have a power BI formula which I have in excel working correctly but in PBI is not getting right results: what this formula needs to do is to count working hours between 2 dates with 2 restrictions "1: considering working hours (08:00:00 to 18:00:00) and 2: let weekends and holidays out of the counting: This is what I expected to have as a result:

07-03-2016 08:00:00        07-03-2016 18:00:00 =     10,00

07-03-2016 10:00:00        07-03-2016 19:00:00 =     8,00

07-03-2016 11:00:00        07-03-2016 19:00:00 =     7,00

07-03-2016 12:00:00        07-03-2016 19:00:00 =     6,00

07-03-2016 01:00:00        07-03-2016 04:00:00 = 0,00

07-03-2016 01:00:00        07-03-2016 04:30:00 = 0,00

07-03-2016 23:00:00        08-03-2016 09:30:00 = 1,50

07-03-2016 01:00:00        17-03-2016 11:00:00 = 90,00

04-03-2016 17:30:00        09-03-2016 20:30:00 = 30,50

07-03-2016 18:00:00        07-03-2016 19:30:00 = 0,00

07-03-2016 17:59:00        07-03-2016 18:00:00 = 0,02

07-03-2016 00:00:00        07-03-2016 03:00:00 = 0,00

07-03-2016 00:00:00        07-03-2016 03:30:00 = 0,00

07-03-2016 00:00:00        07-03-2016 04:00:00 = 0,00

07-03-2016 23:00:00        08-03-2016 09:30:00 = 1,50

07-03-2016 23:00:00        08-03-2016 10:00:00 = 2,00

07-03-2016 23:00:00        08-03-2016 10:30:00 = 2,50

This is the pbix I have:

https://www.dropbox.com/s/h0d1lhzq5r903if/time%20difference%20workhours.pbix?dl=0