cancel
Showing results for
Did you mean:
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 help

2 ACCEPTED SOLUTIONS

Accepted Solutions
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 appreciated

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 again

12 REPLIES 12
Highlighted
Super User

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

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

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

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

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

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

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

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 appreciated

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"

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

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

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

Announcements

#### Challenge: Can You Solve These?

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

#### Community News & Announcements

Get your latest community news and announcements.

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

#### 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
Recent signins: