Calculate NON-Work Days between two dates

SteffanieJ — Mon, 24 Jun 2024 18:20:07 GMT

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.

Re: Calculate NON-Work Days between two dates

bhanu_gautam — Mon, 24 Jun 2024 18:31:01 GMT

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]
)
)

topic Re: Calculate NON-Work Days between two dates in DAX Commands and Tips

Calculate NON-Work Days between two dates

Re: Calculate NON-Work Days between two dates