cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
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.

 

https://drive.google.com/open?id=1RmBqHxvPOYZ-wTEplvaOlln_ajjnnKP8

 

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

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.

Thanks In Advance.

 

Dax Formula What I used:

TBL_Date=DATESBETWEEN('Calender Table'[Date].[Date],MasterData[Invoice Date],MasterData[Payment Date])
var TBL_FinalDate=
ADDCOLUMNS(TBL_Date,"Working day",IF(WEEKDAY([Date],2)>=6,0,1),
"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))Capture1.JPGCapture2.JPG

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






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

Invoice DatePosted DatePayment DateDate DueType of supplierInvoice Received on
13-06-201931-07-201906-12-201913-07-20193rd Party12-07-2019
15-04-201912-08-201906-12-201915-05-20193rd Party08-08-2019
22-10-201930-10-201913-12-201921-11-20193rd Party29-10-2019
24-10-201930-10-201913-12-201923-11-20193rd Party29-10-2019
31-10-201907-11-201916-12-201930-11-20193rd Party31-10-2019
29-10-201912-11-201913-12-201928-11-20193rd Party06-11-2019
31-10-201928-11-201906-12-201930-11-20193rd Party08-11-2019
04-11-201912-11-201904-12-201904-12-20193rd Party11-11-2019
05-11-201915-11-201906-12-201905-12-20193rd Party11-11-2019
31-10-201928-11-201906-12-201930-11-20193rd Party12-11-2019
31-10-201918-11-201913-12-201930-11-20193rd Party12-11-2019
31-10-201918-11-201913-12-201930-11-20193rd Party12-11-2019
01-11-201918-11-201930-12-201901-12-20193rd Party12-11-2019
31-10-201918-11-201913-12-201930-11-20193rd Party13-11-2019
08-11-201918-11-201930-12-201908-12-20193rd Party14-11-2019
28-05-201928-11-201906-12-201927-06-20193rd Party15-11-2019
12-11-201918-11-201930-12-201912-12-20193rd Party15-11-2019
31-10-201920-11-201913-12-201930-11-20193rd Party18-11-2019
01-10-201920-11-201913-12-201931-10-20193rd Party19-11-2019
04-10-201920-11-201913-12-201903-11-20193rd Party19-11-2019
05-11-201927-11-201906-12-201905-12-20193rd Party21-11-2019
06-11-201927-11-201906-12-201906-12-20193rd Party21-11-2019
09-11-201927-11-201906-12-201909-12-20193rd Party21-11-2019
14-11-201927-11-201906-12-201914-12-20193rd Party21-11-2019
15-10-201927-11-201912-12-201914-11-20193rd Party21-11-2019
01-12-201901-12-201913-12-201931-12-20193rd Party22-11-2019
01-12-201901-12-201913-12-201931-12-20193rd Party22-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-313062-31305                       -31,307
5-313002-31305                       -31,307
20-312842-31305                       -31,307
3-312984-31301                       -31,305
3-312981-31301                       -31,302
1-312982-31299                       -31,301
10-312892-31299                       -31,301
-9-312998-31292                       -31,300
13-312841-31298                       -31,299
2-312933-31295                       -31,298
2-312933-31295                       -31,298
4-312913-31295                       -31,298
2-312892-31292                       -31,294
2-312853-31287                       -31,290
42426520                           -247
0111                               -  
181911                               -  
12512611                               -  
16816911                               -  
81022                               -  
121422                               -  
575922                               -  
0333                               -  
3633                               -  
4733                               -  
101333                               -  
151823                                 1
182123                                 1
5813                                 2
101424                                 2
2927                                 5
214612                                 6
1221381016                                 6
8271319                                 6
6271521                                 6
0929                                 7
2232310                                 7

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

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