Return Time based on conditions

[Anonymous]

I have calculated column below that returns count of job bookings based on the job booking datetime where if booking made after 12midday AND picked up by the close of business SAME day, return Yes else No.

JB>12 = VAR _HOUR = HOUR(FreightForward[JOB_BOOKING_DATETIME]) RETURN
IF( _HOUR >= 12 && _HOUR <= 24, "Yes","No")

I need to modify measure based on the following:

if booking made after 12midday AND picked up by the close of business NEXT day, return Yes else No (i.e. booking after 12midday, picked up next day by 11.59pm Note: measure should also include bookings picked up after 12midday on the same day)

Hope that makes sense. Appreciate any help, thanks.

[amitchandak · Accepted solution]

@Anonymous , Try a formula like

JB>12 = VAR _HOUR = HOUR(FreightForward[JOB_BOOKING_DATETIME])
var _pickup = date(year(FreightForward[JOB_BOOKING_DATETIME]), month(FreightForward[JOB_BOOKING_DATETIME]), Day(FreightForward[JOB_BOOKING_DATETIME])+1)+time(23:59:59)

RETURN
IF( _HOUR >= 12 && _HOUR <= 24 && FreightForward[JOB_PICKUP_DATETIME] <=_pickup, "Yes","No")

[Anonymous]

Thank you @amitchandak, much apprecaited