Forum Discussion
DATEDIFF - Exclude weekends and holidays
Please add a calendar table to your data model using the following expression
Calendar = CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2020, 12, 31 ) )
Change the start date and end dates as necessary.
Further, please add one calculated column to your calendar table.
DayNumber = WEEKDAY('Calendar'[Date],1)The '1' in WEEKDAY formula counts Sunday as 1 and Saturday as 7.
Now add the following calculated column to your transaction table.
Weekends =
CALCULATE (
COUNTROWS ( 'Calendar' ),
FILTER (
'Calendar',
AND (
'Calendar'[Date] >= 'Transaction'[Date Demand],
'Calendar'[Date] <= 'Transaction'[Date Presented]
)
),
OR ( 'Calendar'[DayNumber] = 1, 'Calendar'[DayNumber] = 7 )
)In this Weekends formula, I am excluding Sundays and Saturdays using the numbers 1 and 7.
Now you can reduce the number of weekends from the net date difference you have calculated earlier.
Also please ensure that none of the weekend dates are appearing in the holidays table. Otherwise, those days will be double counted.
Anonymous unfortunately I cannot have a calendar table here.
I need to perform all calc within this one table.
How can I do this?