Error retuned

[LAN1984]

I am getting an error returned instead of Orange in the below formula

= Table.AddColumn(#"Added Conditional Column31", "Work Order Color Final", each

    if [#"Work Order Received - Actual"] > [#"Work Order Received - Target"] then "Red"

    else if [#"Work Order Received - Actual"] <= [#"Work Order Received - Target"] then "#00563B"

    else if [Today] > [#"Work Order Received - Target"] then "Orange"

    else if [#"Work Order Received - Target "] <= [#"Work Order Received - Projected"] then "#50C878"

    else null

)

Need the formula to return

Red if Actual is after Target

00563B if Actual isbefore or equal to Target

Orange if Today is after Target

50C878 if Target is qqual to or before Projected, otherwise null

When I use just the Orange portion I get the correct return

[v-csrikanth]

Hi @LAN1984 

We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @LAN1984 
I wanted to follow up since I haven't heard from you in a while. Have you had a chance to try the suggested solutions?
If your issue is resolved, please consider marking the post as solved. However, if you're still facing challenges, feel free to share the details, and we'll be happy to assist you further.
Looking forward to your response!

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @LAN1984 

We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.

[Omid_Motamedise]

@LAN1984  What is the error massage you face

[SacheeTh · Accepted solution]

I think Your formula has a couple of issues: 

The condition for "Orange" (Today > [#"Work Order Received - Target"]) is placed after checking if Actual <= Target, which means it never gets evaluated.
There is an extra space in [#"Work Order Received - Target "], which can cause errors.
The sequence of conditions should be logically ordered to ensure each case is properly checked.

Corrected M:

= Table.AddColumn(#"Added Conditional Column31", "Work Order Color Final", each
    if [#"Work Order Received - Actual"] > [#"Work Order Received - Target"] then "Red"
    else if [#"Work Order Received - Actual"] <= [#"Work Order Received - Target"] then "#00563B"
    else if Date.From(DateTime.LocalNow()) > [#"Work Order Received - Target"] then "Orange"
    else if [#"Work Order Received - Target"] <= [#"Work Order Received - Projected"] then "#50C878"
    else null
)

 Fixes & Improvements:

1. Ensured "Orange" condition is properly evaluated before checking if Target is <= Projected.
2. Used Date.From(DateTime.LocalNow()) to get today's date in Power Query.
3. Removed extra space in [#"Work Order Received - Target "].

Now, this should return:

• "Red" if Actual is after Target
• "#00563B" if Actual is before or equal to Target
• "Orange" if Today is after Target
• "#50C878" if Target is equal to or before Projected
• null otherwise.

[LAN1984]