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...
Need Help
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 🙂