cancel
Showing results for
Did you mean:
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 ID Updated First Touch Date Closed On Calendar Date Country HolidayVSNonHoliday WeekEndVsWeekDay NetWorkDays DesiredNetworkDays 1 5/8/2020 5/8/2020 Italy 1 Weekday 1 1 2 5/8/2020 5/18/2020 Italy 0 Weekday 10 6 3 5/8/2020 5/12/2020 Netherlands 1 Weekend 0 3 4 5/8/2020 5/15/2020 Spain 1 Weekday 8 6 5 5/8/2020 5/20/2020 Germany 1 Weekday 13 9 6 5/8/2020 5/14/2020 Netherlands 1 Weekend 0 5

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

4 REPLIES 4
Highlighted
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...

My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
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!

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

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

Announcements

#### August 2020 Community Challenge: Can You Solve These?

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

#### Community Blog

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

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

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

Top Solution Authors
Top Kudoed Authors