Forum Discussion
Dynamic Conditional Formatting: Green text for YOY growth, Red text for YOY decline
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!
sorry, I gave you a bad DAX measure
Try this ...
Click here to download a solutionRAG colour =
VAR myyear = SELECTEDVALUE(yourtable[Year])
VAR prevyear = myyear -1VAR myval = SUM(yourtable[Value])
VAR prevval =CALCULATE(SUM(yourtable[Value]),ALL(yourtable),yourtable[Year] = prevyear )RETURNIF( myval >= prevval, "Green", "Red")
8 Replies
speedrampsSuper User
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 😀
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)
- 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
sorry, I gave you a bad DAX measure
Try this ...
Click here to download a solutionRAG colour =
VAR myyear = SELECTEDVALUE(yourtable[Year])
VAR prevyear = myyear -1VAR myval = SUM(yourtable[Value])
VAR prevval =CALCULATE(SUM(yourtable[Value]),ALL(yourtable),yourtable[Year] = prevyear )RETURNIF( myval >= prevval, "Green", "Red") 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,
