Conditional formatting based on two other columns

[the606]

Hi,

I have a data table containing numbers. These correspond to some SQL that works out how days have passed from another date.

I have a calculated column that shows how many days have passed since the orginal date until today.

I want to highlight a cell in red if two criteria are met 

• The column is a zero 0 (no date entered).
• The elapsed time from the original date is 2 or above.

I want to highlight the same cell in green if the following criteria are met

• The column is a zero
• The elapsed time is less than 2.

I have tried to create a measure bbut with no luck, any help would be greatly appreciated.

[Angith_Nair]

Hi @the606 ,

Let's say the new calculated column name is Column1. You have to create a measure like this..

Measure =
VAR diff =
    DATEDIFF ( Table[orginal date], TODAY (), DAY )
RETURN
    IF (
        OR ( ISBLANK ( Table[Column1] ), diff >= 2 ),
        "#bf212e",
        IF ( OR ( Table[Column1] = 0, diff < 2 ), "#18a845" )
    )

Use this measure in conditional formatting as a field value option.

[the606]

Hi,

all of the fields are now red.

Colour Instruction to first site visit =

VAR diff =

DATEDIFF ( Query1[Instruction], TODAY (), DAY )

RETURN

IF (

OR ( ISBLANK ( Query1[KPI_1_Working_Days_from_Instruction_to_First Site_Visit] ), diff >= 2 ),

"#bf212e",

IF ( OR ( Query1[KPI_1_Working_Days_from_Instruction_to_First Site_Visit] = 0.00, diff < 2 ), "#18a845" )

)

I only want it to colour if it is greater than 2 from the orginal date and contains 0.00

Thanks very much

[Angith_Nair]

If possible then could you please share the pbix file so that I can look into it..?

[amitchandak]

@the606 , Create a new column like

New column =
var _diff = datediff([orginal date], today(), day)
return
Switch ( True(),
isblank([orginal date]) && [column] =0 , "Red",
[column] =0 , "Green"
_diff > 2, "Red" ,
"Green"
)

Use in conditional formatting with "Field Value" Option with Max aggregation

refer video for steps : https://www.youtube.com/watch?v=RqBb5eBf_I4

[the606]

Hi,

im getting a an error "failed to resolve name 'diff. It is not a valid table, variable or fuction name.

Colour Instruction to first site visit =

var _diff = datediff(Query1[Instruction], today(), day)

return

Switch ( True(),

isblank(Query1[Instruction]) && Query1[KPI_1_Working_Days_from_Instruction_to_First Site_Visit] =0 , "Red",

Query1[KPI_1_Working_Days_from_Instruction_to_First Site_Visit] =0 , "Green",

diff > 2, "Red" ,

"Green"

)

Any ideas?

Thanks very much