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.

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      Icey
      Community Support
      4 years ago

      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's avatar
        Anonymous
        Not applicable
        4 years ago

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