Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Power Query Formula's for Dates RAG Status

Hello i'm looking to add some custom collumns to highlight Red, Amber,Yellow & White from a Date collum in Power BI.   So basically, my date collumn is called "Fix Target"   Anything Before today...
General Comment
Need Help
Show and Tell
  • mahenkj2's avatar
    mahenkj2
    4 years ago

    Hi Anonymous ,

    Incase you would like to add custom column, one way is as below:

     

     

    Output is as below:

     

    Formulae I used:

    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"

     

    Just ensure proper functionality.

     

    If you intended color formatting to your date column, just use this custom column in formatting logic.

     

    Hope it helps.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Sorry this just shows my column as an error? 

  • mahenkj2's avatar
    mahenkj2
    Solution Sage
    4 years ago

    Hi Anonymous ,

     

    Oh I see another message from you. Please click on Error cell, you should be able to see the error description, try to resolve that or please ask here. Additionally, check about your date column, does it contain null or error, if yes, can you remove that?

    Else in your current formula need to give a check for null dates. First pls check and confirm in detail.