Forum Discussion
Conditional formatting not working
Hi mateusz_ta ,
Please follow the below approach,
write a measure like below,
BG color = IF(MAX(FACT_Orders[Order Date])<=TODAY() && MAX(FACT_Orders[Order Date])>=EDATE(TODAY(),-6),"Green","Red")use this measure in the Background color formula,then the result will be as you expected as below
Thanks.
You’re right – conditional formatting rules cannot be applied directly to a date column. A common workaround is to create a calculated column or a measure that returns the number of months between the given date and TODAY().
For example, you could create something like: MonthsDiff = DATEDIFF ( Table[YourDate], TODAY(), MONTH )
Then, in the conditional formatting settings, choose Format style → Rules and base the rules on this numeric field:
If MonthsDiff < 6 → Green
Otherwise → Red
This way the formatting logic is driven by a numeric value, which works correctly in the matrix.
That is exactly my first approach. The column 'StatusColor' is such a calculated column. The string value (red, green...) depends on the relative date. It shows the correct value. But then, when I try to create a conditional formatting based on the column 'StatusColor', the wrong color appears.
If you want to return the text value (e.g. StatusColor) that corresponds to the latest date within the context of Make and Country, you can use a measure like this:
COLOR =
VAR MaxDate =
CALCULATE (
MAX ( Table[price_date_max] ),
ALLEXCEPT ( Table, Table[Make], Table[Country] )
)
RETURN
CALCULATE (
MAXX ( VALUES ( Table[StatusColor] ), Table[StatusColor] ),
FILTER (
Table,
Table[price_date_max] = MaxDate
)
)