Targetdate acquired based on 3 dates

[fluffy2cake]

HI all,

first of all i'm a beginner at powerBI.

I've searched google and microsoft, but no answer yet.

So , I have a "startdate" and "enddate" and a "targetdate" of a service.  Im looking for a way where it can be shown if the targetdate has been acquired or not.

Can anyone here help me?

[Anonymous · Accepted solution]

Hi @fluffy2cake ,

You can try formula like below:

M = 
VAR curStart =
    MAX ( 'Table'[StartDate] )
VAR curEnd =
    MAX ( 'Table'[EndDate] )
RETURN
    IF (
        MAX ( 'Table'[Target] ) > curStart
            && MAX ( 'Table'[Target] ) < curEnd,
        "Achieved",
        "Not Achieved"
    )

You can also consider using conditional formatting for additional visual cues. For example, you could set the font color of the "background" visualization to green if the goal is not achieved, or red if it is.

If the above scenario does not meet your expectations, please refer to the following link to provide the relevant test data and screenshots of the expected results to facilitate me to answer your questions as soon as possible.

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[fluffy2cake]

Hi Adam,

my apologies for not responding sooner to your excellent suggestion. Life happened etc..

I hope you'd still be willing to help me solve this problem. Im partway there but the result is that all record are deemed "not acchieved" with this code. I can see in my source there are records that should result in "achieved".

I edited your code to match my tables as follows:

Doorlooptijd = VAR curStart = MAX ( 'Incidents'[creationDate] ) VAR curEnd = MAX ( 'Incidents'[closureDate] ) RETURN IF ( MAX ( 'Incidents'[targetDate]] ) > curStart && MAX ( 'Incidents'[targetDate]] ) < curEnd, "Achieved", "Not Achieved" )

As far as I see I did not make any mistakes and the program itself did not warn me of an error in the syntax. Im wondering thoug as the date syntax has a timestamp (30-01-2023 13:28:00) , if thats what it is stumbling on?  I set all date fields to short date and it didnt change anything

Perhaps you have some suggestions?

Thanks 🙂