Forum Discussion
Comparing Date portion of datetime fields
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.
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.
Greg_Deckler Thanks for the detailed explanation - I thought that was just an Excel thing - not Power BI too! Learn something new every day.
What will it do for dates before 12/30/1899 ? And does it have the same leap year error built in as Excel? (I'm guessing yes from the date you provided).
AllisonKennedy Well, it used to handle them really poorly where it would add 1900 to the value or something bizarre like that. However now it appears that dates prior to 12/30/1899 just go negative so 12/29/1899 is -1, etc. I believe it has the same leap year built in like Excel but would need to confirm. Also, Excel starts at 1/1/1900 as 1, so not exactly the same.