cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Re: NETWORKDAYS with multiple country holidays

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
Highlighted
Super User I
Super User I

Re: NETWORKDAYS with multiple country holidays

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
Highlighted
Helper III
Helper III

Re: NETWORKDAYS with multiple country holidays

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

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors