Forum Discussion
Calculate NON-Work Days between two dates
- 2 years ago
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]
)
)
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]
)
)