Forum Discussion
DAX if Function Help
Hi,
I have a formula that does not implement my logic correctly. I have 3 columns with Date/Time values:
- Job booking datetime (no blanks)
- Attempted Acceptance Datetime (including blank cells)
- Acceptance Datetime (including blank cells)
The logic is:
if Job Booking DateTime < 12 midday AND Attempted Acceptance Scan DateTime && Acceptance DateTime occur on the SAME DAY as Job Booking DateTime, then output "YES", else "NO"
Here is current formula that I need tweaked:
VAR Time1_ = HOUR(FF_August_Dataset[JOB_BOOKING_DATETIME])
VAR Time2_ = HOUR(FF_August_Dataset[ATTEMPTED_ACCEPTANCE_DATETIME])
VAR Time3_ = HOUR(FF_August_Dataset[ACCEPTANCE_DATETIME])
VAR Date1_ = INT(FF_August_Dataset[JOB_BOOKING_DATETIME])
VAR Date2_ = INT(FF_August_Dataset[ATTEMPTED_ACCEPTANCE_DATETIME])
VAR Date3_ = INT(FF_August_Dataset[ACCEPTANCE_DATETIME])
VAR SameDate_ = (Date1_ = Date2_) && (Date2_ = Date3_)
RETURN
IF( SameDate_ && Time1_ < 12 && Time2_ < 12 && Time3_ < 12, "Yes", "No")
Appreciate any help, thanks.
4 Replies
You can just drop the conditions (and VARs) you don't need:
VAR Time1_ = HOUR(FF_August_Dataset[JOB_BOOKING_DATETIME]) VAR Date1_ = INT(FF_August_Dataset[JOB_BOOKING_DATETIME]) VAR Date2_ = INT(FF_August_Dataset[ATTEMPTED_ACCEPTANCE_DATETIME]) VAR Date3_ = INT(FF_August_Dataset[ACCEPTANCE_DATETIME]) VAR SameDate_ = (Date1_ = Date2_) && (Date2_ = Date3_) RETURN IF( SameDate_ && Time1_ < 12 , "Yes", "No")
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
AlB thank you for responding. I just wasn't sure which conditions / VARS to change but I'll give it a try.
AlB my new logic states that Time2 and Time3 have to be on the SAME date/day as Time1 so instead of <12, do I insert <23:59 or <0:00?
IF( SameDate_ && Time1_ < 12 && Time2_ < 12 && Time3_ < 12, "Yes", "No")
