Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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" )
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!
Works like a charm!!!
Thank you very much!
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:
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!
Proud to be a Super 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" )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
51 | |
27 | |
24 | |
13 | |
8 |
User | Count |
---|---|
74 | |
56 | |
47 | |
16 | |
12 |