RAG status formula query

Question

Hi,

I am trying to put in a RAG status field which references the following fields (% complete, Finish Date, Plan in Place)

I'll be using the following conditions

If the % complete is 100% then the result is C (for complete

If the % complete is less than 100% but the finish date is still in the future then the result is G for Green

If the % complete is less than 100% and the Finish date is before todays date but there is a plan in place (the plan in place field is a Y or N dropdown) then the result is A (for Amber)

If the % complete is less than 100% and the Finish date is before todays date and there is no plan in place (the plan in place field is a Y or N dropdown) then the result is R (for Red)

Any help with the formula needed would be greatly appreciated

Thanks

Answer 1

Ginsterman,

Just for reference, since you are only looking for a "yes" or "no" for the "plan in place" custom field, a Flag field seems more appropriate (i.e. no need to create a value list). So, while waiting for you to respond to my latest post, I assumed the Flag1 field is used for the "plan in place" and I used the Text1 field for the graphicical indicator field. Here is the formula, using the fields I chose, to do what you want. However, if you really have your heart set on using the Text1 field for your "plan in place" field, then you'll need to tweak the formula to look for "yes" or "no" instead of true and false and of course put the formula in whichever other field you are using for your graphical indicator.

Give it a test, let me know if I missing anything.

John

Answer 2

Hi John, its a custom field called "Plan in Place", this has a dropdown where only Y or N can be selected

Answer 3

Ginsterman,

Which field are you using for "plan in place"? It will be easier to write the formula for you.

John

Answer 4

Thanks Mukesh, but that doesn't answer my question

Answer 5

Hi ,

I'm Mukesh and I will be happy to help you out with your question.

I would suggest you to post your question in the powerbi community where like minds meet and you can get a faster and accurate reply.
https://community.powerbi.com/

You can try the below formula and see if that helps
if [Fix Target]=Date.From(DateTime.LocalNow()) then "Amber" else if [Fix Target]=Date.AddDays(Date.From(DateTime.LocalNow()),1) then "Yellow" else if [Fix Target]>Date.AddDays(Date.From(DateTime.LocalNow()),1) then "White" else "Red"

I hope this information helps. If you have any questions please let me know and I will be glad to help you out.

--If the reply is helpful, please Upvote and Accept it as an answer--

Best regards
Mukesh