Forum Discussion

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

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

Community Support

4 years ago

Hi Anonymous ,

Try this:

Working Days (with Calendar Job Booking table) =
VAR t1 =
    IF (
        NOT ( ISBLANK ( [JOB_BOOKING_DATETIME] ) )
            && NOT ( ISBLANK ( [ACCEPTANCE_DATETIME] ) ),
        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_

If this doesn't work, in order to solve the problem for you faster, Could you create a sample .pbix file for me? Then I can create the expression you need directly. Please don't contain any sensitive information.

Reference: How to provide sample data in the Power BI Forum - Microsoft Power BI Community

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

Not applicable

4 years ago

sent you pm with link

Icey

Community Support

4 years ago

Hi Anonymous ,

I create a column and a measure, please check if they are what you want:

Column:

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 )
RETURN
    IF (
        ISBLANK ( [ACCEPTANCE_DATETIME] )
            || [JOB_BOOKING_DATETIME] > [ACCEPTANCE_DATETIME],
        BLANK (),
        Days_
    )

Measure:

Measure - Working Days (with Calendar Job Booking table) =
VAR t1 =
    CALENDAR (
        MAX ( 'FreightForward v2'[JOB_BOOKING_DATETIME] ),
        IF (
            ISBLANK ( MAX ( 'FreightForward v2'[ACCEPTANCE_DATETIME] ) )
                || MAX ( 'FreightForward v2'[JOB_BOOKING_DATETIME] )
                    > MAX ( 'FreightForward v2'[ACCEPTANCE_DATETIME] ),
            MAX ( 'FreightForward v2'[JOB_BOOKING_DATETIME] ),
            MAX ( 'FreightForward v2'[ACCEPTANCE_DATETIME] )
        )
    )
VAR t2 =
    FILTER (
        ADDCOLUMNS (
            t1,
            "IsWorkDay_",
                LOOKUPVALUE (
                    'Calendar Job Booking'[WorkingDay],
                    'Calendar Job Booking'[Date], [Date]
                )
        ),
        [IsWorkDay_] = "Yes"
    )
VAR Days_ =
    COUNTROWS ( t2 )
RETURN
    IF (
        ISBLANK ( MAX ( 'FreightForward v2'[ACCEPTANCE_DATETIME] ) )
            || MAX ( 'FreightForward v2'[JOB_BOOKING_DATETIME] )
                > MAX ( 'FreightForward v2'[ACCEPTANCE_DATETIME] ),
        BLANK (),
        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.

Anonymous
Not applicable
4 years ago
Thanks but I noticed some Working Day values are incorrect e.g. Customer no 91832861 Job datetime 5/10/2021 11:00pm, Acceptance datetime 6/10/2021 2:10pm Working Days = 2
Expected result should be 0 days (15hrs)?
Also can you please edit logic where if elapsed time between Job datetime and Acceptance datetime less than 24hours, return 0

Icey

Community 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
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
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
Community 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
Not applicable
4 years ago
Hi Icey , I'm getting the following error when applying your custom column

Forum Discussion

DateDiff between two dates excluding weekends and holidays

Related content

DATEDIFF excluding weekends

Previous Day Balance - Excluding Weekends

DATEDIFF DAX

Datediff with filters to exclude weekends

Exclude weekends from Date Measure