topic Re: Date time comparison with NOW() function in DAX Commands and Tips

Date time comparison with NOW() function

Anonymous — Tue, 03 Jan 2023 09:57:46 GMT

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?

Re: Date time comparison with NOW() function

johnt75 — Tue, 03 Jan 2023 10:46:26 GMT

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" )

Re: Date time comparison with NOW() function

ValtteriN — Tue, 03 Jan 2023 10:59:49 GMT

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/

Re: Date time comparison with NOW() function

Anonymous — Tue, 03 Jan 2023 11:02:09 GMT

Works like a charm!!!

Thank you very much!

Re: Date time comparison with NOW() function

Anonymous — Tue, 03 Jan 2023 11:04:43 GMT

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!