topic Re: DAX Function To Compare 3 Different Dates-Timestamps Columns in DAX Commands and Tips

DAX Function To Compare 3 Different Dates-Timestamps Columns

Anonymous — Wed, 11 Sep 2019 09:24:39 GMT

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)

Job Booking Date
Attempted Acceptance Date
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:)

Re: DAX Function To Compare 3 Different Dates-Timestamps Columns

AlB — Wed, 11 Sep 2019 09:38:31 GMT

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

Re: DAX Function To Compare 3 Different Dates-Timestamps Columns

Anonymous — Wed, 11 Sep 2019 09:44:45 GMT

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??

Re: DAX Function To Compare 3 Different Dates-Timestamps Columns

AlB — Wed, 11 Sep 2019 10:31:45 GMT

Should be the same. The DAX engine is the same in both cases

Re: DAX Function To Compare 3 Different Dates-Timestamps Columns

Anonymous — Wed, 11 Sep 2019 11:35:14 GMT

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....

Re: DAX Function To Compare 3 Different Dates-Timestamps Columns

AlB — Wed, 11 Sep 2019 14:24:05 GMT

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

Re: DAX Function To Compare 3 Different Dates-Timestamps Columns

Anonymous — Wed, 11 Sep 2019 20:14:47 GMT

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:)

Re: DAX Function To Compare 3 Different Dates-Timestamps Columns

Anonymous — Thu, 12 Sep 2019 00:14:36 GMT

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")

Re: DAX Function To Compare 3 Different Dates-Timestamps Columns

Anonymous — Sun, 15 Sep 2019 07:19:02 GMT

AlB sorry to bug you again but just wanting to follow up on this issue when you have time please. Thanks.

Re: DAX Function To Compare 3 Different Dates-Timestamps Columns

AlB — Sun, 15 Sep 2019 11:18:53 GMT

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

Re: DAX Function To Compare 3 Different Dates-Timestamps Columns

Anonymous — Sun, 15 Sep 2019 12:33:08 GMT

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.

Re: DAX Function To Compare 3 Different Dates-Timestamps Columns

AlB — Mon, 16 Sep 2019 08:14:45 GMT

Anonymous

I'll try to have a look later on. Where is the dataset? Has the post been deleted?

Re: DAX Function To Compare 3 Different Dates-Timestamps Columns

Anonymous — Mon, 16 Sep 2019 22:09:01 GMT

AlB its ok I've managed to fix issue myself after some manual testing. Thanks again:)