Conditionally formatting a date field

[nbarjesteh]

I am having a problem conditoinally formatting a date colum after I have selected do show the "latest" date.  The color column (based on field) is not matching with the correct date.  Any suggestions or alternative methods to fix?  Thanks!!!

[v-yalanwu-msft · Accepted solution]

Hi, @nbarjesteh ;

You could create a measure ,then use contiditional format.

1.create a measure .

Measure = 
var _diff=DATEDIFF(MAX('Table'[Order Date]),TODAY(),MONTH)
return SWITCH(TRUE(),_diff<1,"white",_diff<2,"yellow",_diff<3,"orange","red")

2.use conditional format.

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@nbarjesteh , Logic is not very clear, but you need to create a color measure 

example

Colour =
SWITCH(TRUE(),
max('Table'[Month Year]) = "Jan-2020", "red",
Max('Table'[Month Year]) < "Feb-2020", "orange",
//keep on adding
"green")

Measure =
Switch(true(),
max(Table[Column]) = "R1" , "Red" ,
max(Table[Column]) = "R2" , "Green" ,
max(Table[Column]) = "R3" , "Pink "
)

and use that is conditional formatting using field value option

PowerBI Abstract Thesis: How to do conditional formatting by measure and apply it on pie?: https://youtu.be/RqBb5eBf_I4

[nbarjesteh]

I tried to create a color measure column from DAX below and use it to base the color of the date;

sorry-I do not understand your example:

Color column = SWITCH(true(),

DATEDIFF(today(),Sheet1[Order Date],MONTH)>-2 && DATEDIFF(today(),Sheet1[Order Date],MONTH)<=-1, "yellow",

DATEDIFF(today(),Sheet1[Order Date],MONTH)>-3 && DATEDIFF(today(),Sheet1[Order Date],MONTH)<=-2, "orange",

DATEDIFF(today(),Sheet1[Order Date],MONTH)>-14 && DATEDIFF(today(),Sheet1[Order Date],MONTH)<=-3, "red",blank())