Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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 t...
Need Help
Show and Tell
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Assume you have the following transaction table.

 

Table Name: Transaction

REFDate DemandDate Presented
10001-04-201825-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.

REFDate DemandDate PresentedDateDifferencesNoOfHolidaysNet Date Difference
10001-04-201825-12-201826839229

 

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      Anonymous unfortunately I cannot have a calendar table here.

      I need to perform all calc within this one table. 

      How can I do this?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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.