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

Networking Days excluding weekends and holidays

Hello,

 

I am trying to calculate the networking days between two dates and excluding holidays and weekends.

Below is a screenshot of the data:

Case IDUpdated First Touch DateClosed On Calendar DateCountryHolidayVSNonHolidayWeekEndVsWeekDayNetWorkDaysDesiredNetworkDays
15/8/20205/8/2020Italy1Weekday11
25/8/20205/18/2020Italy0Weekday106
35/8/20205/12/2020Netherlands1Weekend03
45/8/20205/15/2020Spain1Weekday86
55/8/20205/20/2020Germany1Weekday139
65/8/20205/14/2020Netherlands1Weekend05

 

Currently, with our formula we are finding the results in the column Networkdays but it is not correct, what we want in is column desired network days. We followed the logic in this video https://www.youtube.com/watch?v=dSHwQATBxgE and then we tried the following formula:

NetWorkDays =
VAR TBL_Date= CALENDAR('Case Data'[Updated First Touch Date],'Case Data'[Closed On Calendar Date])
Var TBL_FinalDate= ADDCOLUMNS(TBL_Date,"WorkingDay",IF([WeekEndVsWeekDay]="Weekday",1,0))
Return
SUMX(TBL_FinalDate,IF([WorkingDay]=1&&[HolidayVSNonHoliday]=1,1,0))
 
- The WeekEndVsWeekday column represents if the day is a working day or the weekend based on the day of the week and the country. Since different countries have different weekends, this column is based on those conditions.
- The Holidays vs nonholiday column represents holidays based on countries. So 1 means it is not a holiday and 0 means it is a holiday.
 
So to conclude, what we want is to calculate the networking days between the Updated First Touch Date and the Closed On Calendar Date excluding holidays and weekends.
 
looking forward to your help.

 

4 REPLIES 4
Highlighted
Super User IV
Super User IV

Re: Networking Days excluding weekends and holidays

@Dim24 , have you already create a calendar with Workdays that exclude holiday and weekend .

 

Then refer to this file page2/workday diff

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

 

This is an extension of my blog in import mode: https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Differ...

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User IV
Super User IV

Re: Networking Days excluding weekends and holidays

@Dim24 - There is a Quick Measure for this already in the gallery:

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


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

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 Super User!




Highlighted
Frequent Visitor

Re: Networking Days excluding weekends and holidays

@Greg_Deckler - Thanks for the reply. I applied your Dax Formula but I am still left with the challenge where some countries have the weekend on Friday and Saturday. So the solution below only works in scenarios where the weekend is Saturday and Sunday. 

NetWorkDays = 
VAR Calendar1 = CALENDAR(MAX(NetWorkDays[created date]),MAX(NetWorkDays[review date]))
VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date])

 Ta make it even harder I have more than 500 holidays so in your holiday formula, 

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])

it is convenient if one has a few holidays but in my case with more than 500, this solution doesn't work.

 

I am building on the basis of these formulas, I need to figure out how to integrate the different type of weekends and the + 500 holidays.

 

Thanks again for your help

Highlighted
Frequent Visitor

Re: Networking Days excluding weekends and holidays

@amitchandak . Thanks for your reply. I am creating a calendar in my formula in the form of a variable. My issue is that I need to include in my formula different types of weekends, in some countries it is Saturday & Sunday. In others, it is Friday, Saturday. In addition, I need to include +500 holidays...Your solution doesn't work for me but thanks for the support

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors