Forum Discussion

fluffy2cake's avatar
fluffy2cake
Frequent Visitor
2 years ago
Solved

Targetdate acquired based on 3 dates

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?

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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-Forum/ba-p/963216

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


    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's avatar
      fluffy2cake
      Frequent Visitor
      2 years ago

      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 🙂