cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lore256
Frequent Visitor

Difference of hours between 2 dates without Weekend & Holiday

Hello,

 

I'm still a new on Power BI and I looking for a solution everywhere but I didn't found the answer.

 

My problem is: I need to calculate the difference of hours between two dates, but the thing is that I need to calculate only the working days (without weekends and bank holiday).Capture.PNG

I already have this table:Capture2.PNG

Can somebody help me? Please 

 

Best Regards

Lore 

 

 

 

 

4 REPLIES 4
Super User IV
Super User IV

Re: Difference of hours between 2 dates without Weekend & Holiday

Check out my Net Work Days Quick Measure:

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362

 


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

lore256
Frequent Visitor

Re: Difference of hours between 2 dates without Weekend & Holiday

Hello @Greg_Deckler,

 

Thank you for your answer. But I have already seen your solution and I would like this 

NetWorkDaysHoursMinutes

and this

NetWorkDaysHolidays = 
VAR Calendar1 = CALENDAR(MAX(NetWorkDays[created date]),MAX(NetWorkDays[review date]))
//VAR Holidays = DATATABLE("Date",DATETIME,{{}})
VAR Holidays1 = DATATABLE("Date",DATETIME,
    {
        {"12/25/2017 12:00:00 AM"}
    })
VAR Calendar2 = EXCEPT(Calendar1,Holidays1)
VAR Calendar3 = ADDCOLUMNS(Calendar2,"WeekDay",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar3,[WeekDay]<6),[Date])

 in the same formula. 

 

Thank you again 

 

Best regards,

Lore

Super User IV
Super User IV

Re: Difference of hours between 2 dates without Weekend &amp; Holiday

Well, you just combine the formulas then:

 

NetWorkDaysHolidaysDaysHoursMinutes = 
VAR Calendar1 = CALENDAR(MAX(NetWorkDays[created date]),MAX(NetWorkDays[review date]))
VAR Holidays1 = DATATABLE("Date",DATETIME,
    {
        {"12/25/2017 12:00:00 AM"}
    })
VAR Calendar2 = EXCEPT(Calendar1,Holidays1)
VAR Calendar3 = ADDCOLUMNS(Calendar2,"WeekDay",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar3,[WeekDay]<6),[Date]) & " Days " & HOUR(MOD(MAX(NetWorkDays[review date]) - MAX(NetWorkDays[created date]),1)) 
& " Hours " & MINUTE(MOD(MAX(NetWorkDays[review date]) - MAX(NetWorkDays[created date]),1)) & " Minutes"
Red is from NetWorkDaysHolidays, Orange is from NetWorkDaysHoursMinutes and Green are tweaks. I did notice there is an error in the formula so I am correcting that.

 

Red is from NetWorkDaysHolidays, Orange is from NetWorkDaysHoursMinutes and Green are tweaks. I noticed that there is actually an error in the NetworkDAysHoursMinutes formula so I corrected that and will correct it in the original post as well.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User IV
Super User IV

Re: Difference of hours between 2 dates without Weekend &amp; Holiday

I corrected the formula and posted the new formula as well to the Quick Measure gallery.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors