Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
kyrpav
Helper IV
Helper IV

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.

1 ACCEPTED SOLUTION

@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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

7 REPLIES 7
mahoneypat
Employee
Employee

I believe you can use the INT( ) function for that.  

 

INT([First Date]) <= INT([SecondDate])

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


AllisonKennedy
Super User
Super User

@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?


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

i will keep the current calculation i do not want to make date as string and in general i need the time unfortunatly

@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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@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).


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

@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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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. 

AllisonKennedy_0-1629663317603.png

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

 

This is the kind of nerdy stuff I love learning!


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.