Color a column based on the values of another column

[user365576]

Hello everyone,

I want the column Next PRD Date to be colored based on the values of the Actual Delivery Date column. More specifically, if the date is earlier than the corresponding date (same row) in the Actual Delivery Date column, then i want the cell in the Next PRD Date to be colored green (indicative of desired scenario). Alternatively, if the date is later i want it to be colored red (indicator of unwanted script).

Any guesses how can I implement it?

[bhanu_gautam]

@user365576 , You can create new calculate column and use it in rules

DateComparison =
IF (
'YourTable'[Next PRD Date] < 'YourTable'[Actual Delivery Date],
"Green",
"Red"
)

Go to the Report View.
Select the visual (e.g., table or matrix) that contains the "Next PRD Date" column.
Click on the dropdown arrow next to the "Next PRD Date" column in the Values area of the visualizations pane.
Select Cell elements > Background color or Font color.

PBIX is attached 

[user365576]

Both the solutions you gave me don't work correctly. Some dates are colored the way they are supposed to but some others don't

[bhanu_gautam]

Can you explain which are not coloured the way they are supposed it is just sample data , you can check measure and condition in conditional formatting if you need to change something 

[user365576]

Take a look at the screenshots below which show some examples of the incorrect implementation:
1) DateComparison Column

2) Color Formatting

[danextian · Accepted solution]

hI @user365576 ,

You created a DateComparison calculated column by comparing a measure with a column. While that is not necessarily incorrect, the [Next PRD Date] in the rows of your calulcated column might be comparing a different date than what it appears in the visual thus you're getting an unexpected result. 13/12/204 is not earlier than 8/7/2024.  Use a measure so the two dates are compared within the context of the matrix visual.

IF (
    [Next PRD Date] < SELECTEDVALUE ( 'Open SO Orders'[Actual Delivery Date] ),
    "Later",
    "Earlier"
)

The formula above is very similar to what i initially proposed.

[user365576]

This actually worked, but what about the dates that are same? They are labeled as "Later" while i want them null. Can you help me with this one too?

[Anonymous]

Hi @user365576 ,

Based on the description, try to add the if condition.

IF (
    [Next PRD Date] < SELECTEDVALUE ( 'Open SO Orders'[Actual Delivery Date] ),
    "Later",
    IF([Next PRD Date] > SELECTEDVALUE ( 'Open SO Orders'[Actual Delivery Date]),
    "Earlier", "NULL")
)

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bhanu_gautam]

Can you check data type or just export both the date columns in excel and share here so, that I can check at my end

[danextian]

Hi @user365576 ,

Select field value from the conditional formatting options and use this measure:

Color =
IF (
    SELECTEDVALUE ( 'table'[Column1] ) < SELECTEDVALUE ( 'table'[Column2] ),
    "green"
)
--replace green with a  hexadecimal, RGB or RGBA value
--some color names do not have a  built-in color equivalent in Power BI, example is fuschia