topic Re: Comparing Two Date Column it's return wrong value in DAX Commands and Tips

Comparing Two Date Column it's return wrong value

Anonymous — Mon, 25 May 2020 06:59:00 GMT

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

Re: Comparing Two Date Column it's return wrong value

nandukrishnavs — Mon, 25 May 2020 07:17:25 GMT

Anonymous

Try this calculated column

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

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

Also, make sure the Requested Closed Date column type as Date

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

Here is my latest blog

https://community.powerbi.com/t5/Community-Blog/Dynamic-Page-Navigation-Based-on-User-Login/ba-p/1097786

Re: Comparing Two Date Column it's return wrong value

Anonymous — Mon, 25 May 2020 07:47:37 GMT

nandukrishnavs i have tried but same value i'm getting

Re: Comparing Two Date Column it's return wrong value

nandukrishnavs — Mon, 25 May 2020 08:29:38 GMT

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 🙂

Re: Comparing Two Date Column it's return wrong value

Anonymous — Mon, 25 May 2020 08:21:59 GMT

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)

Re: Comparing Two Date Column it's return wrong value

Anonymous — Mon, 25 May 2020 08:22:43 GMT

nandukrishnavs yes it's Date Format column

Re: Comparing Two Date Column it's return wrong value

nandukrishnavs — Mon, 25 May 2020 08:33:35 GMT

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 🙂

Re: Comparing Two Date Column it's return wrong value

Anonymous — Mon, 25 May 2020 09:45:36 GMT

nandukrishnavs Thanks for Sharing it's Working Perfect