Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

DAX Function To Compare 3 Different Dates-Timestamps Columns

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)

 

  1. Job Booking Date
  2. Attempted Acceptance Date
  3. 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:)

 

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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

View solution in original post

Anonymous
Not applicable

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

View solution in original post

12 REPLIES 12
AlB
Super User
Super User

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  Datanaut

Anonymous
Not applicable

@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

Anonymous
Not applicable

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....BEFORE 12 SCAN Error.PNG

 

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  Datanaut

Anonymous
Not applicable

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

Anonymous
Not applicable

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

Anonymous
Not applicable

@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  Datanaut

Anonymous
Not applicable

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? 

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors