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

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

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

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

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

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

      • 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