cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
qsmith83 Regular Visitor
Regular Visitor

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 helpSmiley Happy

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
qsmith83 Regular Visitor
Regular Visitor

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

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 appreciatedSmiley Happy

View solution in original post

qsmith83 Regular Visitor
Regular Visitor

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

@AlB  its ok I've managed to fix issue myself after some manual testing. Thanks againSmiley Happy

View solution in original post

12 REPLIES 12
Highlighted
Super User
Super User

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

Hi @qsmith83 

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

qsmith83 Regular Visitor
Regular Visitor

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

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

Super User
Super User

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

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

qsmith83 Regular Visitor
Regular Visitor

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

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

Super User
Super User

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

 

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

qsmith83 Regular Visitor
Regular Visitor

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

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 appreciatedSmiley Happy

View solution in original post

qsmith83 Regular Visitor
Regular Visitor

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

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

qsmith83 Regular Visitor
Regular Visitor

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

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

Super User
Super User

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

 

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 217 members 2,196 guests
Please welcome our newest community members: