Conditional formatting based on years within timeframe

[PuddleRunna]

Hi all,

I'm wondering whether there's a faster/better practice approach to what I'm doing:

Basically I'm trying to create a report on a hardware lifecycle.

Lifecycle looks like this: 

So everything within a 5year timespan is fine, anything older needs a refresh or has no support

My Table looks like this:

Mapping | Generation | Last Supported Build (Refresh)

X280	2018
L470	2017	21H2
T490	2019
T460	2016	20H2
P50	2014	1809
L460	2016	20H2
X250	2015	1909

Now what I'd like to be able to do dynamically is use conditional formatting based on where a model is in it's lifecycle, i.e. if it's within 5 Years of e.g. YEAR(TODAY()) it's green, if it's in year 6 it's amber, if it's >6 Years it's red.

Column Generation is set as Date (yyyy). I also have a measure for Current year = Year(TODAY()). I have created a new column as 

Aging = [Current Year]-'HW Lifecycle'[Generation] which gives me the difference, in my case values from 1-9 as 2012 is the oldest year.

In conditional formatting I have used this, but I'm wondering whether there's a smoother way to do this, e.g. via a measure?

(I have a different measure foe something else that I created to be used in conditional formatting: 

Color Today = if(FIRSTNONBLANK('Waves'[Date],TODAY()) <today(),"#01B8AA","#fd817e"), so maybe there's something similar for the above scenario. I just couldn't figure it out)

Any tips are greatly appreciated 🙂

[v-kelly-msft · Accepted solution]

Hi  @PuddleRunna ,

Yes,you could use below dax expression:

Color Today = if(MAX(Aging)>5&&MAX(Aging)<10,"#d10c08",
                 if(MAX(Aging)=6,"#f5e042",
                   if(MAX(Aging)>=1%&&MAX(Aging)<=5,"#63f542",Blank())))

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[PuddleRunna]

Hi Kelly,

thanks a lot!! 🙂

Had to remove the '%' in the last line and corrected my own mistake of  ">5 <10" to ">=7 <10" (in case someone is looking for something similar and is gonna copy this)

It still behaves oddly for the last line, e.g. it doesn't mark lines that have an aging value of 3. But I'm sure I'll figure this out from here.

So thanks again!