Done Date > Join Date = Breach/Yes

[stackedcreative]

Have data for new employee join (hire) dates and done date of services for that employee. 

If the done date is greater than the join date, Breach = Yes.  AKA- the services weren't completed before the user joined the company. 

The current formula is:

Breach = IF('TABLE1'[DoneDate] > 'TABLE1'[JoinDate],"Yes","No")

The problem is, we have services that are still not complete, even though the join date has passed.  Need to show any Join Dates that have passed already as "Yes" to breach, even if there's no done date.    

For example, given today is 10/08/2020:

Join date: 10/01/2020

Done date: null

Breach = Yes

How would I write this?

[AlB]

@stackedcreative 

It looks like you are using DAX syntax in power query?? The previous solution was in DAX, i don't understand...

Additionally M is case sensitive and won't accept IF but needs if.  You need if ... then ... else

Anyway, there are quite a few things but  first you need to clarify why you are using DAX syntax in PQ. It doesn't make sense.

Always assume that it is a problem with your code rather than a system bug and you'll be correct at least 99% of the time, if not more 😊

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[stackedcreative]

@AlB You are right.  I was in the wrong section.  Had to jog my memory lol.  Thank you!!! 

[AlB · Accepted solution]

Hi @stackedcreative 

Breach = IF('TABLE1'[DoneDate] > 'TABLE1'[JoinDate] || ISBLANK('TABLE1'[DoneDate]),"Yes","No")

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[stackedcreative]

Hi @AlB - while this formula works, it is pulling in all nulls as Breach = yes.  Breach should only be if the Join Date has already passed... so, the join dates it's bringing in, like 10/18/2020, hasn't occured yet, but it's marking it Yes as breach.  Any ideas?  So, is blank IF the date is less than or equal to today()... I'm not sure how to properly add this. 

[AlB]

@stackedcreative 

I'm not sure I understand what you need. If what you want is only yes if JoinDate <=TODAY then:

Breach = IF(('TABLE1'[DoneDate] > 'TABLE1'[JoinDate] || ISBLANK('TABLE1'[DoneDate])) && 'TABLE1'[JoinDate] <= TODAY(),  ,"Yes","No")

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[stackedcreative]

Hey @AlB .  You helped me with this before, and it worked.  Has something changed with syntax in Power Query?  I try adding it now, and I get an error about Token Literal, and TokenRightParen.  I have tried numerous times.

When I type it out manually, it now adds # before the data source name, and double quotes around it.  Like this:

IF((#"NASA Onboard Svc Dtl with Links"[DoneDate] > #"NASA Onboard Svc Dtl with Links"[JoinDate], || ISBLANK(#"NASA Onboard Svc Dtl with Links"[DoneDate])) && #"NASA Onboard Svc Dtl with Links"[JoinDate] <= TODAY(), ,"Yes","No")

I've had a *lot* of random weird issues with Power BI that I've had to directly contact Microsoft about, and am wondering is this a result of an update, a random issue, or what?

Thank you so much! 

[stackedcreative]

That did the trick @al !  Thank you. 

[stackedcreative]

Thank you SO much!!!!  Fast reply and correct!!  You rock! 😁