Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I have a formula that does not implement my logic correctly. I have 3 columns with Date/Time values:
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.
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 I changed the formula to reflect the new logic but I'm skeptical of the output as per screenshot below which is heavily skewed based on 72k rows of data. Is my formula correct?
Here is link to full dataset if you are able to replicate from your end.
=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_ < 24 && Time3_ < 24, "Yes", "No")
@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")
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
9 | |
7 | |
6 | |
6 |
User | Count |
---|---|
29 | |
11 | |
11 | |
10 | |
6 |