Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
Am using Powerpivot and I have 3 columns, each with date & time scans in them. The 3 columns occur in the exact sequence as shown below: (i.e. Job booking date occurs first, then Attempted Acceptance Date, then Acceptance Date)
I have a calculated column called BEFORE 12 SCAN and I need a function to determine the following logic:
if Job Booking Date < 12:00 midday, AND Attempted Acceptance Date < 12:00 midday (on the same day) AND Acceptance Date < 12:00 midday (on the same day), then output "YES", else "NO" in BEFORE 12 SCAN column
Note: Job booking has NO blank cells but Attempted Acceptance Date and Acceptance Date may have blank cells.
Appreciate any help:)
Solved! Go to Solution.
Yes that worked and powerpivot does include = at beginning by default.
Sorry its my first time seeing such a long formula and didn't know you could include variables in a formula which is awesome.
Thank you again for your help, much appreciated:)
@AlB its ok I've managed to fix issue myself after some manual testing. Thanks again:)
Hi @Anonymous
Try this
NewCol = VAR Time1_ = HOUR(Table1[Job Booking Date]) VAR Time2_ = HOUR(Table1[Attempted Acceptance Date]) VAR Time3_ = HOUR(Table1[Acceptance Date]) VAR Date1_ = INT(Table1[Job Booking Date]) VAR Date2_ = INT(Table1[Attempted Acceptance Date]) VAR Date3_ = INT(Table1[Acceptance Date]) VAR SameDate_ = (Date1_ = Date2_) && (Date2_ = Date3_) RETURN IF( SameDate_ && Time1_ < 12 && Time2_ < 12 && Time3_ < 12, "Yes", "No")
Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.
Cheers
@AlB thank you for the quick reply. FYI am using powerpivot in Excel. Your formula structure/syntax looks different, will it still work or do I have to modify it before applying it??
Should be the same. The DAX engine is the same in both cases
I pasted formula and replaced Table 1 with my table name but I get the following error
"Failed to resolve name 'NewCol' it is not a valid table, variable or function name"
Sorry I'm a bit of a newbie and still learning....
NewCol is the name of the new column. The DAX code for the column is the rest so enter everything without the "NewCol ="
(or maybe with the = at the beginning, I don't remember if it's included by default in Power Pivto when you define a calculated column)
VAR Time1_ = HOUR(Table1[Job Booking Date]) VAR Time2_ = HOUR(Table1[Attempted Acceptance Date]) VAR Time3_ = HOUR(Table1[Acceptance Date]) VAR Date1_ = INT(Table1[Job Booking Date]) VAR Date2_ = INT(Table1[Attempted Acceptance Date]) VAR Date3_ = INT(Table1[Acceptance Date]) VAR SameDate_ = (Date1_ = Date2_) && (Date2_ = Date3_) RETURN IF( SameDate_ && Time1_ < 12 && Time2_ < 12 && Time3_ < 12, "Yes", "No")
Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.
Cheers
Yes that worked and powerpivot does include = at beginning by default.
Sorry its my first time seeing such a long formula and didn't know you could include variables in a formula which is awesome.
Thank you again for your help, much appreciated:)
@AlB sorry to bug you again but I misinterpreted my logic. The correct logic should be:
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"
Your formula below fyi
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")
@AlB sorry to bug you again but just wanting to follow up on this issue when you have time please. Thanks.
Hadn't we looked at this already? If I understand correctly what you are after, you can just drop two of the conditions and their variables:
VAR Time1_ = HOUR(Table1[Job Booking Date]) VAR Date1_ = INT(Table1[Job Booking Date]) VAR Date2_ = INT(Table1[Attempted Acceptance Date]) VAR Date3_ = INT(Table1[Acceptance Date]) 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
Hi @AlB yes we did and the formula worked fine but as per my last message, the output result did not look right as it was heavily skewed based on over 72k rows of data. I attached full dataset in my last message and just curious if you were able to replicate it from your end. But if not possible, then its ok. Sorry for the trouble. Appreciate your help thus far.
@Anonymous
I'll try to have a look later on. Where is the dataset? Has the post been deleted?
@AlB its ok I've managed to fix issue myself after some manual testing. Thanks again:)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
20 | |
19 | |
15 | |
15 |
User | Count |
---|---|
47 | |
28 | |
25 | |
17 | |
17 |