cancel
Showing results for
Did you mean:
Helper I

## NETWORKDAYS with multiple country holidays

Hi, I am computing the difference between two working dates to know the lenght of days used, the formula is below;

TAT = CALCULATE(SUM(CalendarTable[WorkingDay]),
DATESBETWEEN(CalendarTable[Date],
'Processed Data'[Date of failure],
'Processed Data'[Date of repair])
)

WorkingDay = SWITCH(CalendarTable[WeekNo],1,0,7,0,1)

But i realized that i forgot to add the holidays in the equation of the working day, on top of that, i have difficulty because my data is across different countries that does not share the same holidays.

I shared the data below.

Thanks in advance for the help.

I have a column for the country in each row so it can be used to filter the TAT computation based on the country holiday list, I guess.

I have been at it for hours and can't seem to figure out how to do it. Any help will be appreciated.

12 REPLIES 12
Helper III

Dear @danextian   ,

Plese help me to get out of this !!!

I want Values Row by row in Calculated column followed same way as you mentioned below and it is not giving me negtive values,Please find the Below Data whichi have and Screen Captures.

Dax Formula What I used:

TBL_Date=DATESBETWEEN('Calender Table'[Date].[Date],MasterData[Invoice Date],MasterData[Payment Date])
var TBL_FinalDate=
"Holiday",IFERROR(LOOKUPVALUE('Holiday-2019'[Holidaycount],'Holiday-2019'[Holidays - 2019,2020],[Date]),0)
)
return
SUMX(TBL_FinalDate,if([Working day]=1&&[Holiday]=0,1,0))
Super User II

Please post a sample data that is not screen caps and a sample table of your expected result.

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Helper III
 Invoice Date Posted Date Payment Date Date Due Type of supplier Invoice Received on 13-06-2019 31-07-2019 06-12-2019 13-07-2019 3rd Party 12-07-2019 15-04-2019 12-08-2019 06-12-2019 15-05-2019 3rd Party 08-08-2019 22-10-2019 30-10-2019 13-12-2019 21-11-2019 3rd Party 29-10-2019 24-10-2019 30-10-2019 13-12-2019 23-11-2019 3rd Party 29-10-2019 31-10-2019 07-11-2019 16-12-2019 30-11-2019 3rd Party 31-10-2019 29-10-2019 12-11-2019 13-12-2019 28-11-2019 3rd Party 06-11-2019 31-10-2019 28-11-2019 06-12-2019 30-11-2019 3rd Party 08-11-2019 04-11-2019 12-11-2019 04-12-2019 04-12-2019 3rd Party 11-11-2019 05-11-2019 15-11-2019 06-12-2019 05-12-2019 3rd Party 11-11-2019 31-10-2019 28-11-2019 06-12-2019 30-11-2019 3rd Party 12-11-2019 31-10-2019 18-11-2019 13-12-2019 30-11-2019 3rd Party 12-11-2019 31-10-2019 18-11-2019 13-12-2019 30-11-2019 3rd Party 12-11-2019 01-11-2019 18-11-2019 30-12-2019 01-12-2019 3rd Party 12-11-2019 31-10-2019 18-11-2019 13-12-2019 30-11-2019 3rd Party 13-11-2019 08-11-2019 18-11-2019 30-12-2019 08-12-2019 3rd Party 14-11-2019 28-05-2019 28-11-2019 06-12-2019 27-06-2019 3rd Party 15-11-2019 12-11-2019 18-11-2019 30-12-2019 12-12-2019 3rd Party 15-11-2019 31-10-2019 20-11-2019 13-12-2019 30-11-2019 3rd Party 18-11-2019 01-10-2019 20-11-2019 13-12-2019 31-10-2019 3rd Party 19-11-2019 04-10-2019 20-11-2019 13-12-2019 03-11-2019 3rd Party 19-11-2019 05-11-2019 27-11-2019 06-12-2019 05-12-2019 3rd Party 21-11-2019 06-11-2019 27-11-2019 06-12-2019 06-12-2019 3rd Party 21-11-2019 09-11-2019 27-11-2019 06-12-2019 09-12-2019 3rd Party 21-11-2019 14-11-2019 27-11-2019 06-12-2019 14-12-2019 3rd Party 21-11-2019 15-10-2019 27-11-2019 12-12-2019 14-11-2019 3rd Party 21-11-2019 01-12-2019 01-12-2019 13-12-2019 31-12-2019 3rd Party 22-11-2019 01-12-2019 01-12-2019 13-12-2019 31-12-2019 3rd Party 22-11-2019

The  Expected result in Excel:

 Invoice Date to Receipt Date(Calender Days ) Invoice Date to Payment Date(Calender Days ) Receipt Date to Posting Date  ( Working Days ) Receipt Date to Payment Date(Working Days) Posting to Payment Date -3 -31306 2 -31305 -31,307 5 -31300 2 -31305 -31,307 20 -31284 2 -31305 -31,307 3 -31298 4 -31301 -31,305 3 -31298 1 -31301 -31,302 1 -31298 2 -31299 -31,301 10 -31289 2 -31299 -31,301 -9 -31299 8 -31292 -31,300 13 -31284 1 -31298 -31,299 2 -31293 3 -31295 -31,298 2 -31293 3 -31295 -31,298 4 -31291 3 -31295 -31,298 2 -31289 2 -31292 -31,294 2 -31285 3 -31287 -31,290 4 24 265 20 -247 0 1 1 1 - 18 19 1 1 - 125 126 1 1 - 168 169 1 1 - 8 10 2 2 - 12 14 2 2 - 57 59 2 2 - 0 3 3 3 - 3 6 3 3 - 4 7 3 3 - 10 13 3 3 - 15 18 2 3 1 18 21 2 3 1 5 8 1 3 2 10 14 2 4 2 2 9 2 7 5 2 14 6 12 6 122 138 10 16 6 8 27 13 19 6 6 27 15 21 6 0 9 2 9 7 22 32 3 10 7

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform October Community Highlights

Check out the top community contributors across all of the communities

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors