Forum Discussion
Help with Custom Column Measure - Datediff between two dates
- 4 years ago
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 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!!
- Anonymous4 years agoNot applicable
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.