Nested IF/AND Statement Help

[SNad_11]

Hello everyone, I am trying to find the best way to translate the below excel formula into PowerQuery in PowerBi. 

The data is project and date information and essentially the formula looks and compares dates against one another to provide a status. I appreciate that the syntax for IF statements in PowerQuery is 'if x then x else x'. 

In Excel it is expressed as follows:

=IFS([@[% Complete]]=1,"Complete",[@[Baseline_Date)]]>=[@[Current Forecast]], "On Track",AND([@[Baseline_Date)]]>TODAY(),[@[Current Forecast]]>[@[Baseline_Date]]),"At Risk",[@[Baseline_Date]]<TODAY(),"Missed"). 

Any help would be much appreciated! 

[SNad_11]

Thanks Bifinity_75! 

That worked for me, also have tried this and this worked too: 

if [Percent_Complete]=1 then "Complete"
else if [Baseline_Date]>= [Forecast_Date] then "On Track"
else if [Baseline_Date]>[Today] and [Forecast_Date]>[Baseline_Date] then "At Risk"
else if [Baseline_Date]<[Today] then "Missed" else ""

[v-henryk-mstf]

Hi @SNad_11 ,

Whether the advice given by @Bifinity_75  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.

Best Regards,
Henry

[Bifinity_75 · Accepted solution]

Hi @SNad_11 , try this formule: assuming the table is named "Table1"

Result = if(Table1[% Complete]=1,"Complete",
            if (Table1[Baseline_Date]>Table1[Current Forecast],"On Track",
                if (Table1[Baseline_Date]>TODAY() && Table1[Current Forecast]>Table1[Baseline_Date],"At Risk",
                    if(Table1[Baseline_Date]<TODAY(),"Missed","-"
                    ))))

 Best regards