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