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 tried but it's return wrong value    can you pls guide me 

 

  • 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     🙂

7 Replies

      • 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         🙂