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 with seperate Date table and I don't have any seperate date table :(

 

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

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    Not much to go on here, but could you create a calculated column and use WEEKDAY() to get the day of the week. Let's say Sunday is 1 and Saturday is 7.

     

    Then you could create a measure like the following:

     

    Measure = ([First Date] - [Second Date])*1. - CALCULATE(COUNT([Column]),FILTER(Table,([WeekDay] = 1 || [WeekDay = 7) && [Date] > [First Date] && [Date] < [Second Date]))

    Something along those lines.

    • 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