Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 🙂
Solved! Go to 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!
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!
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |