Forum Discussion

ashutosh's avatar
ashutosh
Helper I
9 years ago
Solved

Find the difference between dates excluding Weekends

Hi,   I have to calculate the difference between two dates but excluding the weekends (Holiday's may in in future) i.e, Sat & Sun  for now.   I checked many threads here all the solutions are wit...
Need Help
Show and Tell
Tips and Tricks
  • v-caliao-msft's avatar
    v-caliao-msft
    9 years ago

    ashutosh,

     

    I have tested it on my local environment, we can add a calculated column to check if the date is working, and then sum up this calculated column.

    Weekday = WEEKDAY('Table'[Date])
    IsWorkingday = IF('Table'[Weekday]>6||'Table'[Weekday]<2,0,1)

    DateDifference =
    var CurrentDate = CALCULATE(MAX(Table1[Date]))
    var PreviousDate = CALCULATE(MAX(Table1[Date]),FILTER(ALL(Table1),Table1[Date]<MAX(Table1[Date])))
    return
    IF(ISBLANK(PreviousDate),BLANK(),
    CALCULATE(SUM('Table'[IsWorkingday]),FILTER(ALL('Table'),'Table'[Date]>=PreviousDate && 'Table'[Date]<CurrentDate)))

     

    Regards,

    Charlie Liao

ashutosh's avatar
ashutosh
Helper I
9 years ago

Greg_Deckler Thank you for your response.

 

For making it more simple I created a date table, and also created DayInweek as Saturday as 6 & Sunday as 7 and wrote something like you suggested:

 

I am creating a calculated column here.

 

Total Time = 1.0*(Operations_Data[LAST DELIVERED EVENT] - Operations_Data[FIRST IN TRANSIT EVENT]) - CALCULATE(COUNT('Calendar'[IfWorkDay]),FILTER('Calendar','Calendar'[IfWorkDay] = 6 || 'Calendar'[IfWorkDay] = 7 ))

But, I am not getting the proper result. Please suggest

v-caliao-msft's avatar
v-caliao-msft
Microsoft Employee
9 years ago

ashutosh,

 

I have tested it on my local environment, we can add a calculated column to check if the date is working, and then sum up this calculated column.

Weekday = WEEKDAY('Table'[Date])
IsWorkingday = IF('Table'[Weekday]>6||'Table'[Weekday]<2,0,1)

DateDifference =
var CurrentDate = CALCULATE(MAX(Table1[Date]))
var PreviousDate = CALCULATE(MAX(Table1[Date]),FILTER(ALL(Table1),Table1[Date]<MAX(Table1[Date])))
return
IF(ISBLANK(PreviousDate),BLANK(),
CALCULATE(SUM('Table'[IsWorkingday]),FILTER(ALL('Table'),'Table'[Date]>=PreviousDate && 'Table'[Date]<CurrentDate)))

 

Regards,

Charlie Liao

  • ashutosh's avatar
    ashutosh
    Helper I
    9 years ago

    v-caliao-msft 

     

    Thank you so much.

     

    That works great. But I have one more question to ask may be you could answer:

     

    I have 4 columns in my table:

     

    First column

    Second Column

    Third Column

    Fourth Column

     

    I calculte the date diffeerence between First and Fourth but sometimes, First column has null values so insted of first I have to choose Second Column value and same with Third and Fourth (If Fourth is null, I have to choose Third Column value).

     

    How could I proceed in the same example as you suggested above.

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      9 years ago

      You could create two new columns, Five and Six with formulas like:

       

       

      Five = IF(ISBLANK([One]),[Two],[One])

      Six = IF(ISBLANK([Four]),[Three],[Four])

      Then just base everything off of columns Five and Six.

       

    • v-caliao-msft's avatar
      v-caliao-msft
      Microsoft Employee
      9 years ago

      ashutosh,

       

      You could use a IsBlank to check the column, such as

      =IF(IsBlank(Table[Colum1]),Table[Column2],Table[Column1])

       

      Regards,

      Charlie Liao