Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Comparing Two Date Column it's return wrong value

i have using below Dax formula today = IF(VALUE(TODAY()) == test[Request Closed Date] ,"1","0") yesterday = IF(VALUE(TODAY()-1) == test[Request Closed Date],"1","0")    both condition i have tri...
  • nandukrishnavs's avatar
    nandukrishnavs
    6 years ago

    Anonymous 

     

    Yesterday=
IF (
    TODAY ()-1
        = DATE ( YEAR ( testTable[Request Closed Date] ), MONTH ( testTable[Request Closed Date] ), DAY ( testTable[Request Closed Date] ) ),
    1,
    0
)
    Today =
IF (
    TODAY ()
        = DATE ( YEAR ( testTable[Request Closed Date] ), MONTH ( testTable[Request Closed Date] ), DAY ( testTable[Request Closed Date] ) ),
    1,
    0
)

     

    Is this working?

    Also, I have updated my previous post with the sample dataset, see if it is working or not.



    Did I answer your question? Mark my post as a solution!
    Appreciate with a kudos     🙂

Anonymous's avatar
Anonymous
Not applicable
6 years ago

nandukrishnavs    i have tried but same value i'm getting

 

  • nandukrishnavs's avatar
    nandukrishnavs
    Community Champion
    6 years ago

    Anonymous  Make sure the column type is Date.

     

    I have tried to replicate your scenario. 

    Below is the PowerQuery for the sample table

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLVNTDVNTIwMlCK1QFyTVC5+GWNUblGCG4sAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Request Closed Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Request Closed Date", type datetime}})
in
    #"Changed Type"

     

    Calculated columns

     

    Today = IF(TODAY()=testTable[Request Closed Date],1,0)
    Yesterday = IF(TODAY()-1=testTable[Request Closed Date],1,0)

     

     Alternatively

    Yesterday=
IF (
    TODAY ()-1
        = DATE ( YEAR ( testTable[Request Closed Date] ), MONTH ( testTable[Request Closed Date] ), DAY ( testTable[Request Closed Date] ) ),
    1,
    0
)
    Today =
IF (
    TODAY ()
        = DATE ( YEAR ( testTable[Request Closed Date] ), MONTH ( testTable[Request Closed Date] ), DAY ( testTable[Request Closed Date] ) ),
    1,
    0
)

    It is working for me.

    Did I answer your question? Mark my post as a solution!
    Appreciate with a kudos     🙂

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      nandukrishnavs    yes it's   Date Format   column

       

       

      • nandukrishnavs's avatar
        nandukrishnavs
        Community Champion
        6 years ago

        Anonymous 

         

        Yesterday=
IF (
    TODAY ()-1
        = DATE ( YEAR ( testTable[Request Closed Date] ), MONTH ( testTable[Request Closed Date] ), DAY ( testTable[Request Closed Date] ) ),
    1,
    0
)
        Today =
IF (
    TODAY ()
        = DATE ( YEAR ( testTable[Request Closed Date] ), MONTH ( testTable[Request Closed Date] ), DAY ( testTable[Request Closed Date] ) ),
    1,
    0
)

         

        Is this working?

        Also, I have updated my previous post with the sample dataset, see if it is working or not.



        Did I answer your question? Mark my post as a solution!
        Appreciate with a kudos         🙂

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Hi Anonymous ,
     
    Apart from the solution mentioned, you can try this too.
     
    Yesterday = IF (DATEDIFF(Table[Request Closed Date],Today(),DAY) = 1,1,0)
     
    Today= IF (DATEDIFF(Table[Request Closed Date],Today(),DAY) = 0,1,0)
     

    Regards,
    Harsh Nathani

    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)