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 ,

 

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