Forum Discussion
Date time comparison with NOW() function
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?
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" )
4 Replies
johnt75Super User
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" )- ValtteriN
Community Champion
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()-3var _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/
