Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

Net Work Days

Recreates the NETWORKDAYS function from Excel with some bonus measures and one that needs fixed or demonstrates a bug/documentation glitch.   NetWorkDays calculates the number of days between two...
Other
Time Intelligence
juncco888's avatar
juncco888
Advocate I
5 years ago

Greg_Deckler,

 

Thanks for these measures...  They have been bery useful.  I have a Holiday Table that has a date column for all of the holidays.  Is there a way to reference that column and have it add the dates to the Holidays1 variable in this  holiday measure:

 

NetWorkDaysHolidaysDaysHoursMinutes = 
VAR Calendar1 = CALENDAR(MAX(NetWorkDays[created date]),MAX(NetWorkDays[review date]))
//VAR Holidays = DATATABLE("Date",DATETIME,{{}})
VAR Holidays1 = DATATABLE("Date",DATETIME,
    {
        {"12/25/2017 12:00:00 AM"}
    })
VAR Calendar2 = EXCEPT(Calendar1,Holidays1)
VAR Calendar3 = ADDCOLUMNS(Calendar2,"WeekDay",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar3,[WeekDay]<6),[Date]) & " Days " & 
HOUR(MOD(MAX(NetWorkDays[review date]) - MAX(NetWorkDays[created date]),1)) & " Hours " 
& MINUTE(MOD(MAX(NetWorkDays[review date]) - MAX(NetWorkDays[created date]),1)) & " Minutes"