Help with Custom Column Measure - Datediff between two dates

[Anonymous]

I have custom column measure (created by another forum member) that returns Working Days (excluding weekends & holidays) between two dates but its not returning correct result as per screenshot below. 

Here's link to sample report >>https://drive.google.com/drive/folders/1dmmbhdbzySZBr8oTxj9CWsmztwFQ3f8A?usp=sharing

Appreciate any help please. @VahidDM 

Job to Accep Working Days = 
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

[sevenhills]

I did not dig into your full formula, and by quick look you are calculating hours and expecting result as Day, hence you see numbers so big.

It can be simplified as you are doing only date diff.

[Formula updated later in this thread, do NOT use this one]

Job to Accep Working Days v3 = 

var _d_jbook = [JOB BOOKING DATE SHORT] -- DATEVALUE( Format( [JOB_BOOKING_DATETIME], "mm/dd/yyyy") )

var _d_accept = if ( Len([ACCEPTANCE_DATETIME]) <= 0, blank(), DATEVALUE( Format( [ACCEPTANCE_DATETIME], "mm/dd/yyyy") ))
var _d_end = if (ISBLANK(_d_accept) || _d_jbook > _d_accept, _d_jbook, _d_accept)
var _workdays = CALCULATE( countrows( 'Calendar Job Booking'), 
                    FILTER('Calendar Job Booking', 'Calendar Job Booking'[WorkingDay] = "yes"), 
                    DATESBETWEEN('Calendar Job Booking'[Date], _d_end, _d_jbook))
RETURN _workdays -- -1

Note: [ACCEPTANCE_DATETIME]: you can add short date as you did for Job Booking date.

https://www.sqlbi.com/blog/alberto/2019/03/25/using-dax-with-datetime-values/

[Anonymous]

Hi @sevenhills , thanks for this it looks much cleaner and some results look correct however there are still errors as per screenshot. I still need the 'time' aspect of the date to be included in calculation for the following reasons:

• time difference is Job Booking Datetime - Acceptance Datetime
• if Acceptance Datetime > Job Booking Datetime, return negative value is fine
• if Acceptance Datetime is blank, return blank
• if time difference is less than 24hrs, return 0

To achieve this, would it be easier to have 2 custom columns i.e. one for DAYS, and one for HOURS?

[sevenhills]

A) Hours vs Days:

In the past, when I was working on Hospitals data, we decided to have like this, purely based on the business requirements

Small note: we started Elasped time (days) as calculating in hours and then dividing by 24 to convert to decimal days. It was causing some confusion in some scenarios and in the end we decided to diff as days only.  We convinced the business users as two measures like below. Hope it helps for you

Elapsed time in Hours

• If it is less than 3 days i.e., Max 72 hours. If it is more than 72 hours, this will be blank. (not zero)

Elapsed time

• By default used and calculation is in days.
  (1 for less than 24 hours)
• Say in your scenario, it will be blank for blank end dates

B) Back to DAX,  please check the updated formula

Job to Accep Working Days v3.1 = 
var _d_jbook = DATEVALUE( [JOB_BOOKING_DATETIME])
var _d_accept = if ( [ACCEPTANCE_DATETIME] = blank(), blank(), DATEVALUE( [ACCEPTANCE_DATETIME] ))
var _d_end = if (ISBLANK(_d_accept) || _d_jbook > _d_accept, _d_jbook, _d_accept)
var _workdays = CALCULATE( countrows( 'Calendar Job Booking'), 
                    DATESBETWEEN('Calendar Job Booking'[Date], _d_jbook, _d_end), 
                    FILTER('Calendar Job Booking', 'Calendar Job Booking'[WorkingDay] = "yes")
                    )
RETURN if (ISBLANK(_d_accept), blank(), _workdays -1)

Remove "-1" to your needs. Because some does not want decimal days as one day and some want it. I added -1 to remove those decimal days.

Also, since we are using "if" logic to adjust the end date and also for blank date, I hardly think of negative value scenarios 

Hope this will help

[Anonymous]

Hi @sevenhills , thank you for explaining, appreciate it. However some values don't look right unless I'm misintepreting them? As per screenshots (one includes -1, the other excludes -1), can you please explain the difference? Let's use the first row as an example:

Measure including -1

Job booking datetime 16/09/2021 4:00:00pm Acceptance datetime 6/10/2021 2:33:54pm Working Days = 1036 I presume this is 10.36 days? If so, then shouldn't expected result = ~13/14 days? If not, is that result in hours? Then shouldn't it be ~312/336hrs?

Measure excluding -1

Job booking datetime 16/09/2021 4:00:00pm Acceptance datetime 6/10/2021 2:33:54pm Working Days = 1110 I presume this is 11.10 days? If so, then shouldn't expected result = ~13/14 days? If not, is that result in hours? Then shouldn't it be ~312/336hrs?

FYI: I intend to use this measure in a visual that will group job bookings by working days (screenshot below - the measure used includes weekends/holidays)

My apologies for the long post and if I'm missing something obvious in your explanation and calculation, I'm still learning and I appreciate your time with this.

[sevenhills · Accepted solution]

a) Please check your data types of columns used in this DAX if not dates or date times
"[JOB_BOOKING_DATETIME], [ACCEPTANCE_DATETIME]" ... 

b) I provided below is calculated column DAX.  (Sorry, Not measure syntax. R u doing Measure?)

Job to Accep Working Days v3.1 = 
var _d_jbook = DATEVALUE( [JOB_BOOKING_DATETIME])
var _d_accept = if ( [ACCEPTANCE_DATETIME] = blank(), blank(), DATEVALUE( [ACCEPTANCE_DATETIME] ))
var _d_end = if (ISBLANK(_d_accept) || _d_jbook > _d_accept, _d_jbook, _d_accept)
var _workdays = CALCULATE( countrows( 'Calendar Job Booking'), 
                    DATESBETWEEN('Calendar Job Booking'[Date], _d_jbook, _d_end), 
                    FILTER('Calendar Job Booking', 'Calendar Job Booking'[WorkingDay] = "yes")
                    )
RETURN if (ISBLANK(_d_accept), blank(), _workdays -1)

c) If it is a measure, please try this way...

Job to Accep Working Days v3.2 = 
var _d_jbook = DATEVALUE( Minx('FreightForward v2', [JOB_BOOKING_DATETIME]))
var _d_accept = if ( Maxx('FreightForward v2', [ACCEPTANCE_DATETIME]) = blank(), blank(), DATEVALUE( Maxx('FreightForward v2', [ACCEPTANCE_DATETIME] ) ))
var _d_end = if (ISBLANK(_d_accept) || _d_jbook > _d_accept, _d_jbook, _d_accept)
var _workdays = CALCULATE( countrows( 'Calendar Job Booking'), 
                    DATESBETWEEN('Calendar Job Booking'[Date], _d_jbook, _d_end), 
                    FILTER('Calendar Job Booking', 'Calendar Job Booking'[WorkingDay] = "yes")
                    )
RETURN if (ISBLANK(_d_accept), blank(), _workdays -1)

In the measure code above, I am doing is 

-  _d_jbook is getting the Job Booking date time and extracting only date part. 

-  _d_accept is getting the Acceptance date time for non-blank values and extracting only date part
- _d_end is the logic which is to adjust the dates per your logic needs

- _workdays is to calculate using the date table, between the two dates, with working days as "yes"

Returning the value only if the acceptance date is NOT blank

Regards

[Anonymous]

Hi @sevenhills , that measure you provided worked with correct results. The issue was obviously me using the custom column dax instead of a measure. I learned something new. Sorry for dragging this on when it could have been solved much sooner. Thank you again for you help and patience with this, much appreciated.

[sevenhills]

Glad, it worked in the end.

Oops, The title of the post says "Custom Column" so I thought as calculated column. I should have asked you clearly. Happy it got solved. 

[VahidDM]

Hi @Anonymous 

The file size is too big. can you please share a sample of your data and the result you are looking for?

Do you want the Day between those dates or Hours?

Appreciate your Kudos!!

[Anonymous]

Hi @VahidDM , I've reduced dataset for September only. Please try again same link.

Expected result in Days. Also note the following:

• time difference is Job Booking Datetime - Acceptance Datetime
• if Acceptance Datetime > Job Booking Datetime, negative value is fine
• if Acceptance Datetime is blank, return blank
• if time difference is less than 24hrs, return 0

Thank you.

[Greg_Deckler]

@Anonymous Have you tried Net Work Days? Net Work Days - Microsoft Power BI Community