Forum Discussion
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...
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.
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.
Sorry, i'm still getting errors? Not sure why?
Pls share sample file with no sensitive data!