topic Re: Done Date > Join Date = Breach/Yes in DAX Commands and Tips

Done Date > Join Date = Breach/Yes

stackedcreative — Thu, 08 Oct 2020 13:24:49 GMT

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?

Re: Done Date > Join Date = Breach/Yes

AlB — Thu, 08 Oct 2020 13:31:03 GMT

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

Re: Done Date > Join Date = Breach/Yes

stackedcreative — Thu, 08 Oct 2020 13:42:35 GMT

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

Re: Done Date > Join Date = Breach/Yes

stackedcreative — Thu, 08 Oct 2020 15:23:54 GMT

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.

Re: Done Date > Join Date = Breach/Yes

AlB — Thu, 08 Oct 2020 15:38:32 GMT

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

Re: Done Date > Join Date = Breach/Yes

stackedcreative — Thu, 08 Oct 2020 17:58:18 GMT

That did the trick al ! Thank you.

Re: Done Date > Join Date = Breach/Yes

stackedcreative — Fri, 04 Dec 2020 21:34:02 GMT

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!

Re: Done Date > Join Date = Breach/Yes

AlB — Sat, 05 Dec 2020 09:00:54 GMT

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

Re: Done Date > Join Date = Breach/Yes

stackedcreative — Wed, 09 Dec 2020 12:05:40 GMT

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