Conditional formatting not working

[mateusz_ta]

I have a matrix visual with dates as values. If the date is not older than 6 months then the value should be green. However, for example, the first field of the matrix says 2025-08, hence, the background color should be green but it's red. Why? In the 'Background Color Formatting Settings' the 'last value' of 'statusColor' field is chosen which apparently says 'Green' according to 'Show data point as a table' view

Screenshot 1: matrix visual

Screenshot 2: Background ColorFormatting Settings

Screenshot 3: right click on the first value (2025-08) and then: 'show data point as a table'

[v-hashadapu]

Hi @mateusz_ta , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

[Shahid12523]

Your matrix shows the correct date (2025-08), but the background color is wrong because “Last” in conditional formatting doesn’t mean latest date—it just grabs the last row in the current filter context, which might be older (e.g., 2021-08 with Red).
✅ Fix:
Use a DAX measure to get the StatusColor for the latest price_date_max, or sort your table by price_date_max descending so “Last” pulls the right row.
Let me know if you want the exact DAX snippet.

LatestStatusColor =
VAR LatestDate =
CALCULATE(
MAX('YourTable'[price_date_max]),
ALLEXCEPT('YourTable', 'YourTable'[Make], 'YourTable'[Country])
)
RETURN
CALCULATE(
MAX('YourTable'[StatusColor]),
'YourTable'[price_date_max] = LatestDate
)

[pankajnamekar25]

Hello @mateusz_ta 

Try with below DAX for CF

BG Color =
VAR CurrentDate = MAX( FACT_Orders[Order Date] )
RETURN
IF (
CurrentDate >= EDATE ( TODAY(), -6 )
&& CurrentDate <= TODAY(),
"Green",
"Red"
)

If my response helped you, please consider clicking
Accept as Solution ✅ and giving it a Like 👍 – it helps others in the community too.


Thanks,


Connect with me on:

LinkedIn

[Aburar_123]

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.

[BernardoPalma]

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.

[mateusz_ta]

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.

[BernardoPalma]

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
)
)

[BernardoPalma]

Hi mateusz_ta,
You may want to try changing the Format style option to Rules instead of Field value. Then you can explicitly define the conditions for when the background should be green or red (e.g., if the date is within the last 6 months → green, otherwise → red).

This approach ensures the formatting logic is applied directly in the visual, rather than relying on the last field value returned by your measure, which can sometimes lead to unexpected results in a matrix.

[mateusz_ta]

I don't see a conditional formatting rule option that could be applied directly to a column with a date format.