Calculate NON-Work Days between two dates

[SteffanieJ]

Hello!  I am trying to calculate the number of NON-Work Days between two dates but all I can find is how to get working days.  

This is my Excel formula.

I do have a calendar set up with these columns but I don't know how to filter for the 0's in "If Work Day" column.

Any help would be greatly appreciated.

[bhanu_gautam · Accepted solution]

@SteffanieJ , Add a calculated column to add weekends 

IsWeekend =
IF (
WEEKDAY(DateTable[Date], 2) >= 6,
TRUE,
FALSE
)

And one more to identify holiday 

IsHoliday =
IF (
CALCULATE (
COUNTROWS ( Holidays ),
Holidays[HolidayDate] = DateTable[Date]
) > 0,
TRUE,
FALSE
)

And after that Combine weekends and holidays to create a column identifying all non-work day

IsNonWorkDay =
DateTable[IsWeekend] || DateTable[IsHoliday]

Finally, create a measure or a calculated column to calculate the number of non-work days between two dates. Assuming you have a start date and end date in your data model, you can create a measure like this:

NonWorkDaysBetween =
CALCULATE (
COUNTROWS ( DateTable ),
FILTER (
DateTable,
DateTable[Date] >= MIN ( YourTable[StartDate] ) &&
DateTable[Date] <= MAX ( YourTable[EndDate] ) &&
DateTable[IsNonWorkDay]
)
)