Forum Discussion
DATEDIFF - Exclude weekends and holidays
Hi Community,
In this particular example, I do not have a relationship with Calendar Table, this needs to be filtered within the same table. Therefore, I would like to get the DATEDIFF between two dates, and within the function exclude weekends and holidays between these two dates.
Table Data (example below)
REF Date Demand Date Presented
100 01/04/2018 25/12/2018
Table Holidays
-Date
There is an active relationship between Table Data [Date Demand] and Table Holidays [Date].
11 Replies
Assume you have the following transaction table.
Table Name: Transaction
REF Date Demand Date Presented 100 01-04-2018 25-12-2018 And a holidays table
Table Name: Holidays
Date 01-04-2018 08-04-2018 15-04-2018 22-04-2018 29-04-2018 06-05-2018 13-05-2018 20-05-2018 27-05-2018 03-06-2018 10-06-2018 17-06-2018 24-06-2018 01-07-2018 08-07-2018 15-07-2018 22-07-2018 29-07-2018 05-08-2018 12-08-2018 19-08-2018 26-08-2018 02-09-2018 09-09-2018 16-09-2018 23-09-2018 30-09-2018 07-10-2018 14-10-2018 21-10-2018 28-10-2018 04-11-2018 11-11-2018 18-11-2018 25-11-2018 02-12-2018 09-12-2018 16-12-2018 23-12-2018 30-12-2018 The date difference can be calculated by adding the following calculated columns in the Transaction Table.
DateDifferences = ('Transaction'[Date Presented] - 'Transaction'[Date Demand])NoOfHolidays = COUNTROWS ( FILTER ( Holidays, AND ( Holidays[Date] >= 'Transaction'[Date Demand], Holidays[Date] <= 'Transaction'[Date Presented] ) ) )Net Date Difference = 'Transaction'[DateDifferences]-'Transaction'[NoOfHolidays]
This will give the following result in your transaction table.
REF Date Demand Date Presented DateDifferences NoOfHolidays Net Date Difference 100 01-04-2018 25-12-2018 268 39 229 Hi Anonymous
It worked partially, there is the need of excluding the weekends days, and your calculations are not contemplating the exclusion of the weekends. Can you help me getting there please?
Results with Correct Days column, calculated in excel:
Thanks.
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.
v-yulgu-msftMicrosoft Employee
Hi Anonymous ,
Please try this:
Column = VAR _DateDifferences = DATEDIFF ( 'Transaction'[Date Demand], 'Transaction'[Date Presented], DAY ) VAR _NoOfHolidays = COUNTROWS ( FILTER ( Holidays, AND ( Holidays[Date] >= 'Transaction'[Date Demand], Holidays[Date] <= 'Transaction'[Date Presented] ) ) ) VAR _NetDateDifference = _DateDifferences - _NoOfHolidays VAR _weekdayofDemand = WEEKDAY ( [Date Demand], 2 ) VAR _weekdayofPresented = WEEKDAY ( [Date Presented], 2 ) VAR _weeknumofDemand = WEEKNUM ( [Date Demand], 2 ) VAR _weeknumofPresented = WEEKNUM ( [Date Presented], 2 ) VAR _weekendTotal = ( _weeknumofPresented - _weeknumofDemand ) * 2 RETURN IF ( _weekdayofDemand <= 5 && _weekdayofPresented <= 5, _NetDateDifference - _weekendTotal, IF ( ( _weekdayofDemand && _weekdayofPresented <= 5 ) || ( _weekdayofDemand = 7 && _weekdayofPresented = 6 ), _NetDateDifference - _weekendTotal + 1, IF ( _weekdayofDemand = 7 && _weekdayofPresented <= 5, _NetDateDifference - _weekendTotal + 2, IF ( ( _weekdayofDemand <= 5 && _weekdayofPresented = 6 ) || ( _weekdayofDemand = 6 && _weekdayofPresented = 7 ), _NetDateDifference - _weekendTotal - 1, IF ( _weekdayofDemand <= 5 && _weekdayofPresented = 7, _NetDateDifference - _weekendTotal - 2, _NetDateDifference - _weekendTotal ) ) ) ) )By the way, I think adding a calendar table might be the best choice to achieve such a requirement as of now.
Best regards,
Yuliana Gu
v-yulgu-msft
That worked like a charm. Only missing one little thing which is, if [Date Presented] is empty, count the days until the end of selected month (Date Demand).I tried to mix with the logic that I had built but it's behaving the way yours is, when the [Date Presented] is empty, it does not sum until the end of month selected (see img below).
Can you help me achieving that ? Thank you.