Power Query Formula's for Dates RAG Status

[Anonymous]

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

Anything Today = Amber

Tomorrow = Yellow

After tomorrow = White

Any help would be greatly appriciated. 

Thanks, 

[Anonymous]

Sorry this just shows my column as an error? 

[mahenkj2]

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.

[mahenkj2 · Accepted solution]

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.

[Akshay_echo1995]

We also have one condition in which we need to show the date column as Red Amber Green for dates which are older than 7days from Today as green , older than -8 days but less than 14 days as Amber and rest all dates as Red , How can we achive this ?

When using DAX as below it not showing the results correctly = 

IF(MAX('DW Celoxis_TimeEntries'[data']][0]]['date]) >= TODAY() -7 , "Green" ,

IF(MAX('DW Celoxis_TimeEntries'[data']][0]]['date])< TODAY() -7 && MAX('DW Celoxis_TimeEntries'[data']][0]]['date])>= TODAY() -14 , "Yellow" , "Red")).

Can anyone help on this ?

[Anonymous]

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]

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]

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

[mahenkj2]

Pls share sample file with no sensitive data!

[Anonymous]