Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Help with Custom Column Measure - Datediff between two dates

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...
  • sevenhills's avatar
    sevenhills
    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