Dynamic Conditional Formatting: Green text for YOY growth, Red text for YOY decline

[afloyd2123]

Hi team,

I'd like to use dynamic conditional formatting to show green text for YOY (year over year) growth and red text for a YOY decrease. 

For example: 2022 Total Department is less than it was in 2021. Therefore, I'd like to show the text (109) in red.

Thank you!

[afloyd2123]

Perhaps it was user error but I struggled to get that equation to work. I eventually created a Prior_Year measure, then an IF(Current_Year > Prior_Year, "Green", "Red"). Then using that measure as a field value for the font color within conditional formatting, 

[speedramps · Accepted solution]

sorry, I gave you a bad DAX measure

Try this ...

 Click here to download a solution 

RAG colour =

VAR myyear = SELECTEDVALUE(yourtable[Year])

VAR prevyear = myyear -1

VAR myval = SUM(yourtable[Value])

VAR prevval =

CALCULATE(

SUM(yourtable[Value]),

ALL(yourtable),

yourtable[Year] = prevyear )

RETURN

IF( myval >= prevval, "Green", "Red")

[speedramps]

Try my original solution again

but take care to follow the instructions on the conditional formating screen

If you can get it working I will creart an example .... but please try DIY thank you !

[speedramps]

Try this ...

add a measure ....

RAG colour  =

VAR myyear = SELECTEDVALUE(yourtable[year])

VAR prevyear = myyear -1
VAR  myval = SUM(yourtable[value])

VAR prevval = CALCULATE( SUM(yourtable[value]), yourtable[year]) = prevyear)
RETURN
IF( myval  >= prevval, "Green", "Red)

in conditaional formating select font, format style, = Field Value, RAG colour 

Please click thumbs up and accept as solution buttons for such a quick reply. Thanks 😀

[afloyd2123]

It seems that I can't select a measure when I'm using Field Value. Only non-summarized columns.

[AlexisOlson]

In that case, you can have [RAG color] output 1 or 2 (instead of "Green" or "Red") and then use those to format by Rules (instead of Field value)

[afloyd2123]

When I submit what is pictured I receive the error below.

MdxScript(Model) (337, 5) Calculation error in measure 'Premium'[RAG colour]: DAX comparison operations do not support comparing values of type Integer with values of type True/False. Consider using the VALUE or FORMAT function to convert one of the values.

[AlexisOlson]

The error seems to indicate that [RAG color] is outputting True or False instead of 1 or 2.