CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
Reem
Reem

Reputation: 23

How to compare between two dates in power bi query

I have a column named status compared between the End date of the task and each month in the year to return 1 for true, 0 for false

i have used this:

Status = IF ([EndDate].[Date] <= [Month].[Date] , 1, 0)

Date Formatting for [EndDate] and [Month]: March 2001 (MMMM yyyy)

The problem : if End Date = May 2019 and Month = May 2019 its returned 0 ,but its should returned 1

Actual Result enter image description here

Expected Result

enter image description here

Upvotes: 2

Views: 12791

Answers (1)

Jon
Jon

Reputation: 4967

That is the correct logic/syntax, is the date you are using just visually formatted as MMMM yyyy, is the actual date in the dd/mm/yyyy format? as you may be trying to compare the following

[EndDate].[Date] = 01/05/2001 to [Month].[Date] 25/05/2001

What you can do is just pull out the month and year for example

MONTH([EndDate].[Date]) and YEAR([EndDate].[Date]) and MONTH([Month].[Date]) and YEAR( [Month].[Date])

So it would be IF(YEAR([EndDate].[Date]) = YEAR( [Month].[Date]) && MONTH([EndDate].[Date]) = MONTH([Month].[Date]), 1, 0)

Upvotes: 1

Related Questions