Networking Days excluding weekends and holidays

[Anonymous]

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

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.

[Greg_Deckler]

@Anonymous - 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

[Anonymous]

@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

[amitchandak]

@Anonymous , 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-Last-Week/ba-p/1051123

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-Difference-Across/ba-p/934397#M451

[Anonymous]

@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