cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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 II
Super User II

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
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors