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],'FreightForwa...
  • 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.

Icey's avatar
Icey
Icon for Community Support rankCommunity Support
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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Iceythank you, it seems to be working now. Appreciate your time and help with this.

I have one more request please if possible, how do I group 'working days' similar to existing visual as per screenshot below (refer to file shared with you) i.e. where total job bookings >= than 10 days, show 10+?

Note: the top visual using datediff formula including weekends/holidays. Bottom visual using your new calculated column formula

Also interesting to note the significant difference in results using your formula - everything same except I replaced axis with your formula. The decrease is expected when excluding weekends/holidays, but not sure if it should be that significant...

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Jihwan_Kim- are you able to assist please on how to incorporate the 'Working Days' custom column measure with the visual i.e. group by days?

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

     

    You can just create another column like so:

    Working Days Group =
SWITCH ( [Working Days] < 10, CONVERT ( [Working Days], STRING ), "10+" )

     

    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

    Hi Icey , I'm getting the following error when applying your custom column