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

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

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

        sent you pm with link