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

        nandukrishnavs  Thanks for Sharing it's Working Perfect