Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DateDiff between two dates excluding weekends and holidays

I have a holidays table linked to date table and I have 2 calculated columns in the date table:

 

Job to Accep Elapsed TIme Days = (DATEDIFF('FreightForward v2'[JOB_BOOKING_DATETIME],'FreightForward v2'[ACCEPTANCE_DATETIME],DAY ))
----------------------------------------------------------------------------------------------
WorkingDay = IF(CONTAINSSTRING('Calendar Job Booking'[Holiday],"Anniversary") && 'Calendar Job Booking'[IsWorkingDay] = "true","Yes",IF('Calendar Job Booking'[Holiday]=BLANK() && 'Calendar Job Booking'[IsWorkingDay] = "true","Yes","No"))

 

For the 'elapsed time days' measure, how do i exclude weekends and holidays? Jihwan_Kim , any suggestions? Let me know if you require further information.

  • Icey's avatar
    Icey
    4 years ago

    Hi Anonymous ,

     

    In order to better check the calculation results, I modify the expression to just calculate the datediff of hours.

     

    You can use TRUNC to truncates a number to an integer by removing the decimal, or fractional, part of the number, like TRUNC( DateDiff_Hour / 24 ).

    Working Days (with Calendar Job Booking table) = 
VAR t1 =
    CALENDAR (
        [JOB_BOOKING_DATETIME],
        IF (
            ISBLANK ( [ACCEPTANCE_DATETIME] )
                || [JOB_BOOKING_DATETIME] > [ACCEPTANCE_DATETIME],
            [JOB_BOOKING_DATETIME],
            [ACCEPTANCE_DATETIME]
        )
    )
VAR t2 =
    FILTER (
        ADDCOLUMNS (
            t1,
            "IsWorkDay_",
                LOOKUPVALUE (
                    'Calendar Job Booking'[WorkingDay],
                    'Calendar Job Booking'[Date], [Date]
                )
        ),
        [IsWorkDay_] = "Yes"
    )
VAR Days_ =
    COUNTROWS ( t2 ) - 1
VAR StartWorkingDateTime =
    MINX ( t2, [Date] )
VAR EndWorkingDateTime =
    MAXX ( t2, [Date] )
VAR JOB_BOOKING_DATE =
    DATE ( YEAR ( [JOB_BOOKING_DATETIME] ), MONTH ( [JOB_BOOKING_DATETIME] ), DAY ( [JOB_BOOKING_DATETIME] ) )
VAR ACCEPTANCE_DATE =
    DATE ( YEAR ( [ACCEPTANCE_DATETIME] ), MONTH ( [ACCEPTANCE_DATETIME] ), DAY ( [ACCEPTANCE_DATETIME] ) )
VAR DateDiff_Start =
    IF (
        StartWorkingDateTime = JOB_BOOKING_DATE,
        DATEDIFF ( StartWorkingDateTime, [JOB_BOOKING_DATETIME], HOUR )
    )
VAR DateDiff_End =
    IF (
        EndWorkingDateTime = ACCEPTANCE_DATE,
        DATEDIFF ( EndWorkingDateTime, [ACCEPTANCE_DATETIME], HOUR )
    )
VAR DateDiff_Hour =
    IF (
        ISBLANK ( [ACCEPTANCE_DATETIME] )
            || [JOB_BOOKING_DATETIME] > [ACCEPTANCE_DATETIME],
        BLANK (),
        Days_ * 24 - DateDiff_Start + DateDiff_End
    )
RETURN
    DateDiff_Hour

     

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

18 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    Could you share your sample pbix file by sharing the Onedrive link or any other type of link to your sample pbix file?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      sent you pm with link to file

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Jihwan_Kim- I understand you're unable to assist but I appreciate your help so far.

        VahidDMare you able to assist?

  • Icey's avatar
    Icey
    Community Support
    4 years ago

    Hi Anonymous ,

     

    Please check my reply in this similar thread: Work Hours disconsidering holidays and weekends. It should meet your requirements.

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Icey- thanks for sharing that. I tried modifying measure to fit my requirements but I'm getting error due to some blanks as per screenshot. How do I account for blanks? Also for my requirement, I don't need min / max date and I need my result in days, not minutes. 

       

      Working Days (with Calendar Job Booking table) = 
VAR t1 =
    CALENDAR ( [JOB_BOOKING_DATETIME], 'FreightForward v2'[ACCEPTANCE_DATETIME] )
VAR t2 =
    FILTER (
        ADDCOLUMNS (
            t1,
            "IsWorkDay_", LOOKUPVALUE ( 'Calendar Job Booking'[WorkingDay], 'Calendar Job Booking'[Date], [Date] )
        ),
        [IsWorkDay_]
    )
VAR Days_ =
    COUNTROWS ( t2 )
VAR StartWorkingDateTime =
    CONVERT ( MINX ( t2, [Date] ) & " " & TIME ( 8, 0, 0 ), DATETIME )
VAR EndWorkingDateTime =
    CONVERT ( MAXX ( t2, [Date] ) & " " & TIME ( 17, 0, 0 ), DATETIME )
VAR DateDiff_Start =
    IF (
        StartWorkingDateTime < [JOB_BOOKING_DATETIME],
        DATEDIFF ( StartWorkingDateTime, [JOB_BOOKING_DATETIME], MINUTE )
    )
VAR DateDiff_End =
    IF (
        EndWorkingDateTime > [ACCEPTANCE_DATETIME],
        DATEDIFF ( [ACCEPTANCE_DATETIME], EndWorkingDateTime, MINUTE )
    )
    VAR WorkingMinutes = Days_ * 9 * 60 - DateDiff_Start - DateDiff_End
RETURN
    WorkingMinutes / 60

       

       

      • Icey's avatar
        Icey
        Community Support
        4 years ago

        Hi Anonymous ,

         

         

        I tried modifying measure to fit my requirements but I'm getting error due to some blanks as per screenshot. How do I account for blanks? 

         

        For the blanks, what is your calculation logic? Ignore it or use specify datetime?

         

         

         

        Also for my requirement, I don't need min / max date and I need my result in days, not minutes. 

         

        Could this give what you want?

        Working Days (with Calendar Job Booking table) = 
VAR t1 =
    CALENDAR ( [JOB_BOOKING_DATETIME], 'FreightForward v2'[ACCEPTANCE_DATETIME] )
VAR t2 =
    FILTER (
        ADDCOLUMNS (
            t1,
            "IsWorkDay_", LOOKUPVALUE ( 'Calendar Job Booking'[WorkingDay], 'Calendar Job Booking'[Date], [Date] )
        ),
        [IsWorkDay_]
    )
VAR Days_ =
    COUNTROWS ( t2 )
RETURN
    Days

         

         

        Best Regards,

        Icey

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.