Date time comparison with NOW() function

[Anonymous]

Hello Everyone,

I have an issue with comparing two datetime values.
The goal is to mark all the rows of data that are within 6:00:00 AM 3 days ago and 6:00:00 AM today:

As you may see upper expression returns NO for datetime that is within last 3 days and i do not understand why is that.
Two clues:

1. When i am doing IF statement only for IF CurrentRowDate>=Is3Day it returns only datetime rows for past year. Condistion is not meet for current year.

2. Previously report was build for periods midnight to midnight, i wasn't including time part - it worked.

Can someone please explain what is goind on? DateTime column is Date/Time format but maybe it is still the case of different formatting used in expression?

[Anonymous]

Thanks to all of you who participated in the conversation. 

@johnt75  his solution i checked first and it worked so i marked as a solution but @ValtteriN input is also very useful!

[Anonymous]

Works like a charm!!!

Thank you very much!

[ValtteriN]

Hi,

The way you are using FORMAT the NOW() is considered as text in the calculation. This can be tested easily by removing the FORMAT from the dax:

Column2 = var _now =

NOW()

var _3now = NOW()-3

 var _col = 'Table (16)'[Column1]

 return

IF(_col>=_3now && _col<=_now,"Yes","No")


To get full hours you can use this kind of dax: 

Column 3 = var _now = TODAY() + TIME(6,0,0)

var _3now = TODAY()-3 + TIME(6,0,0)

 var _col = 'Table (16)'[Column1]

 return

IF(_col>=_3now && _col<=_now,"Yes","No")

End results:
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[johnt75 · Accepted solution]

I think the problem is that FORMAT is returning a string, so the IF statement is comparing 2 string values. Try

Within last 3 days =
VAR Is3Day =
    TODAY () - 3
        + TIME ( 6, 0, 0 )
VAR IsToday =
    TODAY () + TIME ( 6, 0, 0 )
RETURN
    IF ( 'Table'[DateTime] >= Is3Day && 'Table'[DateTime] <= IsToday, "Yes", "No" )