Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Dynamic Conditional Formatting Based On Previous Year

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

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

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.

conditionalformatYoY.jpg

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

View solution in original post

2 REPLIES 2
jdbuchanan71
Super User
Super User

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.

conditionalformatYoY.jpg

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
Not applicable

That works perfectly! Thank you so much!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.