Forum Discussion
Help with Custom Column Measure - Datediff between two dates
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
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.
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
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.
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.