Comparing Date Fields in PowerBI

[ktran22]

I wanted to create a new column in my PowerBI Dashboard report that compares two date columns (<, >, >=, <=). I need the output for this new date column to either be a date or some sort of binary output (true/fasle, 1 or 0). How can I do this in a PowerBI?

[ktran22]

@DOLEARY85 It did not work. Please see below screenshot

[DOLEARY85]

Do you have a copy of the PBIX file i could look at?

[ktran22]

@DOLEARY85 Here is a screenshot of the tables. I can't share the PBIX file. 

[DOLEARY85]

Okay without seeing the data it might be more difficult, from the look of it each record in the aprroval table links to many in the security. which date are you trying to compare against?

if it's the most recent, try wrapping the knowledge date part in a MAX function.

Column = if('Table'[Column1]=MAX('Table'[Column2]),1,0)

[ktran22]

@DOLEARY85 Does it matter which table I create the calculated column on? See attached. There  can be many different knowledge dates but the approval date is only the most recent shown in that column. Both Approval and Security tables are connected on the Security ID

[DOLEARY85]

It should work if you put it in the security table, i took a sample of the data and ran a test:

Column = if(Security[Knowledge Date] < max(Approval[Approval Date]),1,0)

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[ktran22]

Even in this example the knowledge date is prior to the approval date but yet the formula you wrote is returning a 1

[ktran22]

IT WORKED! Thank you

[ktran22]

@DOLEARY85 Never mind. I am still getting values that are returning true even though the knowledge date of that row is less than the MAX(Approval Date). This is the formula I inputed: 

Approval Check = IF(MAX(Approval[Approval Date]) >= Security[Knowledge Date],1,0). See screenshot below. 

[DOLEARY85]

Check the dates are the same format in both tables

[ktran22]

What format should they be in? Date, true/false, text? 

[DOLEARY85]

No Problem, happy to help 🙂

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[ktran22]

To reiterate, there is only one Approval Date per Security ID but there can be multiple Knowledge Dates per Security ID

[DOLEARY85]

Hi,

you can create a calculated column:

e.g. for if one is equal to the other:  

Column = if('Table'[Column1]='Table'[Column2],1,0)

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍