DATEDIFF - Exclude weekends and holidays

[Anonymous]

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

[v-yulgu-msft]

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

[Anonymous]

@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.

[v-yulgu-msft]

Hi @Anonymous ,

Made some modifications to original formula, please pay attention to the highlighted part.

Column = var _EndDate=IF('Transaction'[Date Presented]=BLANK(),ENDOFMONTH('Transaction'[Date Demand].[Date]),[Date Presented])
         var _DateDifferences = DATEDIFF( 'Transaction'[Date Demand],_EndDate,DAY)
         var _NoOfHolidays = 
COUNTROWS (
    FILTER (
        Holidays,
        AND (
            Holidays[Date] >= 'Transaction'[Date Demand],
            Holidays[Date] <= _EndDate
        )
    )
)
         var _NetDateDifference = _DateDifferences-_NoOfHolidays
         var _weekdayofDemand=WEEKDAY([Date Demand],2)
         var _weekdayofPresented=WEEKDAY(_EndDate,2)
         var _weeknumofDemand=WEEKNUM([Date Demand],2)
         var _weeknumofPresented=WEEKNUM(_EndDate,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)))))

Best regards,

Yuliana Gu

[Anonymous]

@v-yulgu-msft  Thanks for your help:. There's something going on with the exclusion of the weekends, when Date Demand and Date Presented do not fall on the same month. 

Please have a look at the below columns. 'Expected Result' is showing the correct network days between Date Demand and Presented. 

Thank you.

Results here: 

https://we.tl/t-3RcSRnMGA2

[Anonymous]

@v-yulgu-msft 

I think you should factor in the years also. i.e. If the date presented is 2nd Jan 2019, then weeknum will return 1 as the result, and if date demanded is 30th Dec 2018, then weeknum will return 52.

When you calculate weekends using the expression

var _weekendTotal=(_weeknumofPresented-_weeknumofDemand)*2

it will be evaluated as (1-52) = -51 * 2 = -102.

[Anonymous]

Please include a test case with "demand date" in 2018 and "presented date" in 2019.

eg: Demand Date: 15.11.2018 and Presented Date 10.02.2019.

[Anonymous]

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

[Anonymous]

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: 

https://we.tl/t-ca9g4ZTEsz

Thanks. 

[Anonymous]

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.

[MdJ83]

Thanks, this is great but where do I also include this in the original code.

Also if a date start and end date is the same date I still want it in as count as 1 day where the current code is giving a zero.

[Anonymous]

@Anonymous unfortunately I cannot have a calendar table here.

I need to perform all calc within this one table. 

How can I do this?