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.

mahenkj2's avatar
mahenkj2
Solution Sage
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

Hello, Thank you for replying, i seem to be having an error come up when i try to complete this as above? Please see below. 

 

=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"

  • mahenkj2's avatar
    mahenkj2
    Solution Sage
    4 years ago

    Hi Anonymous ,

     

    Power query (M) is case sensitive, so please use proper syntax, like Date.From is written as date.from in your formula which is not correct. Auto correct should help you. Else you may also please go on Mincorsoft documentation for Power query.

     

    The correct syntax for the formula you shown above is:

    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"

     

    Hope it helps.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Sorry, i'm still getting errors? Not sure why? 

      • mahenkj2's avatar
        mahenkj2
        Solution Sage
        4 years ago

        Pls share sample file with no sensitive data!