Forum Discussion
Comparing Date portion of datetime fields
I want to ask if there is an sorted way or any build in way in powerbi to compare two field of datetime where the comparison should be done based only on dates.
So to datetimes:
FirstDate: 2021-08-15 13:30:00 PM
SecondDate: 2021-08-15 10:30:00 AM
If i do :
FirstDate<=SecondDate
i get false cause of time portion which is normal.
In order to bypass it i am doing the next:
DATE(YEAR(FirstDate),MONTH(FirstDate),DAY(FirstDate)<=DATE(YEAR(SecondDate),MONTH(SecondDate),DAY(SecondDate)
Which is working but it is not so elegant. Is there any other built in function that does this think or something that i can do?
In general i am using it in a calculate function to count rows of a table where several other filters have to be applied and one of all is this.
I do not have issue with the function , it is working just i would like to have a smaller solution. I can also not placed to variables cause this is inside calculate function for a complete table.
INT(FirstDate)<=INT(SecondDate)Date Time values are decimal numbers where the integer portion is the number of days since 12/30/1899 and the time portion is the decimal portion and is in fractions of a day hours/24 + minutes/60 + seconds/3600. So, if you use INT to just return the integer portions to do the comparison, you are good to go.
7 Replies
I believe you can use the INT( ) function for that.
INT([First Date]) <= INT([SecondDate])
Pat
kyrpav I don't think it's any shorter or more efficient, but you could use DATEVALUE(FORMAT([date], "YYYYMMDD"))
Also, you can define variables anywhere in your DAX code, even within CALCULATE functions, so not sure what you mean by not being able to use variables?
Finally, do you need the time? You could convert the DateTime to Date in Power Query?
i will keep the current calculation i do not want to make date as string and in general i need the time unfortunatly
INT(FirstDate)<=INT(SecondDate)Date Time values are decimal numbers where the integer portion is the number of days since 12/30/1899 and the time portion is the decimal portion and is in fractions of a day hours/24 + minutes/60 + seconds/3600. So, if you use INT to just return the integer portions to do the comparison, you are good to go.
