Dynamic Conditional Formatting Based On Previous Year

[Anonymous]

Hello there,

Could I get assistance with conditional formatting. I need to be able to change colors based on previous year.  So, if the amount last year is greater than this year then I would like to change the color of this year Red to show that is “bad”, and if last years totals were less than this year then I would like to change the color of this year total to be Green to show that this year we did “good.” I don’t think this is possible with conditional formatting because the values for each order type are constantly changing from year to year. Please let me know if you have any ideas. Thank you in advance!

Some example numbers below.

Order Type	Sales	Sales Last Year	Ship Amount	Ship Last Year
1	$18,000	$16,000	$6,000	$9,000
2	$12,000	$12,500	$7,000	$6,500
3	$20,000	$17,000	$10,000	$12,000

What I would like it to show. 

Order Type	Sales	Sales Last Year	Ship Amount	Ship Last Year
1	$18,000	$16,000	$6,000	$9,000
2	$12,000	$12,500	$7,000	$6,500
3	$20,000	$17,000	$10,000	$12,000

[jdbuchanan71 · Accepted solution]

Hello @Anonymous 

You can write a measure to apply the formatting.

FORMAT Sales Amount = IF ( [Sales Amount] > [Sales Last Year], "GREEN", "RED")

The use it in conditional fomatting to color the text.

You would just need a formatting measure for each column but it should work in all contexts.

FORMAT Sales Last Year = IF ( [Sales Amount] < [Sales Last Year], "GREEN", "RED")

[Anonymous]

That works perfectly! Thank you so much!