Forum Discussion

PuddleRunna's avatar
PuddleRunna
Advocate I
5 years ago
Solved

Conditional formatting based on years within timeframe

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 🙂

  • 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!

2 Replies

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support

    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's avatar
      PuddleRunna
      Advocate I

      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!