Conditional Formatting Comparing Values by Year

[Anonymous]

I have a table showing material utilization percentages over multiple fiscal years. I would like to apply conditional formatting to this table so that if the utilization percentage from one year is greater than the previous year it will show green, and red if it is lower. Below is the table I am working with. Any help you can offer would be greatly appreciated. Thank you in advance for your time!

[Anonymous]

You can start by creating a measure that calculates the difference between one year and another. When you apply the conditional formatting to the field, you can choose to conditionally format that field by a different field.

Ex: Difference2020 = FY2020 - FY2019

Format --> Conditional Formatting --> Choose field in dropdown --> advanced controls --> select rules under "Format by" dropdown --> select Difference2020 under the "Based on field" dropdown --> set your rules

I hope this helps, let me know if you need clarification.

[Anonymous · Accepted solution]

@Anonymous I got it figured out.

First I created a measure using the SAMEPERIODLASTYEAR function to calculate the average utilization from the pervious year

utilizaiton LY = CALCULATE( [utilization] , SAMEPERIODLASTYEAR( [date] ))

Then I created a measure like you suggested to calculate the difference and eliminate false data caused by blanks

difference utilization = IF( ISBLANK( [utilization LY] ), BLANK(), [utlization] - [utilization LY] )