Exclude days from calculation

[leobrand]

I want to pull the 'Dates Off' field into the formula below instead of manually entering them one by one.

NetWorkDays([StartDate], [EndDate]) as "ExcludeWeekendHoliday"

Here is my code, can you help?

[Exclude Days]:
LOAD * INLINE [

'Dates Off',
'1/1/2018',
'1/15/2018',
'2/19/2018',
'3/30/2018',
'4/27/2018',
'5/28/2018',
'6/29/2018',
'7/5/2018',
'7/4/2018',
'8/14/2018',
'9/3/2018',
'10/31/2018',
'11/22/2018',
'11/23/2018',
'12/24/2018',
'12/25/2018',

]
;

tblCalculation:

Load

StartDate,

EndDate,

NetWorkDays([StartDate], [EndDate]) as "ExcludeWeekendHoliday"

Resident Supply ;

Drop Table Supply ;

[jonathandienst · Accepted solution]

Are you asking how to take the [Dates Off] into account in the NetWorkDays() calculation. Then do something like (assuming D/M/YYYY is your default date format):

[Exclude Days]:
LOAD Concat(Chr(39) & [Dates Off] & Chr(39), ',') as Holidays
;
LOAD * 
INLINE [
Dates Off,
1/1/2018,
1/15/2018
...
12/24/2018,
12/25/2018,
]
;

Let vHolidays = Peek('Holidays');
DROP Table [Exclude Days];

NetWorkDays(StartDate, EndDate, $(vHolidays)) as ...

vHolidays should contain a comma separated list of quotes dates.  NetWorkDays() will now consider the dates in vHolidays as non-working, in addition to the weekends.