Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 Ac...
  • Anonymous's avatar
    Anonymous
    7 years ago

    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's avatar
    Anonymous
    7 years ago

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

AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
7 years ago

 

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's avatar
Anonymous
Not applicable
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

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

      • AlB's avatar
        AlB
        Icon for Community Champion rankCommunity Champion
        7 years ago

         

        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