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
v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

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

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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.

     

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

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

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

      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