Forum Discussion

kyrpav's avatar
kyrpav
Helper V
5 years ago
Solved

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: 202...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

    kyrpav 

    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's avatar
Greg_Deckler
Community Champion
5 years ago

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.

AllisonKennedy's avatar
AllisonKennedy
Community Champion
5 years ago

Have confirmed the leap year - Power BI will not allow the 29 Feb, 1900 as a valid date, but Excel does. So starting from March 1, 1900 both Power BI and Excel use the same INT value for dates. 

Whereas Excel does not view anything before Jan 1, 1900 as a valid date. 

 

This is the kind of nerdy stuff I love learning!