Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 upp...
AND
NOW()
  • johnt75's avatar
    johnt75
    3 years ago

    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's avatar
ValtteriN
Icon for Community Champion rankCommunity Champion
3 years ago

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/