DateDiff between two dates excluding weekends and holidays

[Anonymous]

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]

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]

@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]

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.

[Anonymous]

Thanks @Icey but the formula is returning error.."failed to resolve name 'Days'. It is not a valid table, variable, or function name"

Regarding the blanks, just return null or blank

[Icey]

Hi @Anonymous ,

Sorry, I missed an underscore at the end. And again, for the blanks, what is your calculation logic? Ignore it or use specify datetime?

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.

[Anonymous]

No prob, I should have picked up that simple syntax error even for a beginner myself:)

Ok its now returning error due to "start date cannot be later than the end date"

Sorry for not providing enough detail for the logic...the working days difference is a result of (Acceptance datetime - Job booking datetime i.e. Job booking always occurs first). If Acceptance datetime is earlier than Job booking datetime, do nothing (I presume it will just show negative value).

If job booking and/or acceptance datetime is blank, do nothing (which I presume will just show blank?)

[Icey]

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]

sent you pm with link

[Icey]

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]

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 · Accepted solution]

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]

@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...

[Icey]

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]

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

[Anonymous]

@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?

[Jihwan_Kim]

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]

sent you pm with link to file

[Anonymous]

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

@VahidDMare you able to assist?