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 = Red
Anything Today = Amber
Tomorrow = Yellow
After tomorrow = White
Any help would be greatly appriciated.
Thanks,
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.
9 Replies
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.
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"
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.
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 ?
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.
