Conditional Formatting

[Anonymous]

Little Help here, Im using one Measure in a matrix table (CRR = Retention %) and looking at the 2022 CRR and 2021 CRR with Year in the column portion of my table. I want to use conditional formatting to highlight 2022 CRR % only, red = less than PY and green = higher than PY.  I wrote a dax measure that I thought was going to do this but 2021 is showing up red and 2022 is showing up green and the logic is wrong. When I go to delete "red" from the DAX formula, 2022 is all green no matter if its less or greater than 2021. 

Code 

Table Output (with green and red):

Table Output (just green): 

(SEE EITL JUST GREEN FOR ERROR) 

The second table output would be correct if EITL 2022 would have shown up red. 

[AllisonKennedy · Accepted solution]

@Anonymous  Logic wise I would try something like:

Color Measure = 

VAR _CurrentYear = YEAR(TODAY())

VAR _CYmeasure = [Measure]

VAR _PYmeasure = [PYmeasure] -- CALCULATE( [Measure] , DATEADD(Date[Date], -1, YEAR) )

RETURN

IF(  YEAR( Date[Date] ) = _CurrentYear, 
IF ( _CYmeasure > _PYmeasure, "Green", "Red")
)

[AllisonKennedy]

@Anonymous  Your screenshot is small, can you please paste the DAX code direct into a post here?