Multiple Questions..

[sully343]

Hello,

I'm building a "retirement predictor" for work. This is tied into an SQL database to pull a persons date of birth, and their hire date. I converted these dates to "Age" and "Years Service". The age/years service columns are just the basic conditional formatting in PBI. The "first name" column I'm trying to use DAX to create a value and then doing conditional formatting for the value (as recommended here by some). I'm just running into issue with it being ranges/the rules of it.

My measure is:

Condition = MAXX(ViewPersonnel,

IF(ViewPersonnel[Age] >= 50&& ViewPersonnel[originalsvcdate - Copy] >=25, 1,

IF(ViewPersonnel[Age] < 50&& ViewPersonnel[originalsvcdate - Copy] >=25,2

)))

Conditional formatting then looks at "condition" for 1, 2, 3, ect.

I'm trying to apply the following rules, but struggling because it seems to only pick up people under one of the rules and not account for the other...

If age >= 50 AND service >= 25, name is green.

If age < 50 and service >= 25 , name is orange

Age => 45 and service between 20-25, yellow

The second question is I'm also trying to have a slider (or something where you could input a date) that will allow this to "fast forward". IE the starting position would be "current date" and it would add 1-3 years to people's age/time (basically look into the future to see what it will look like in a year or so). If there's another way to accomplish this, that'd work as well. 

[Anonymous]

Hi @sully343 

Can you provide your sample data(remove sensitive info) us with excel or pbix file form ?

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sully343]

Hello,

Here's a sample table. I can't figure out how to attach an excel file (it tells me xls not supported).

First	Last	Rank	Age	originalsvcdate - Copy
Brian	Smith	FR Captain	42.5	24.78
Michael	Turner	FR Lieutenant	51.2	25.01
Greg	Myers	FR Tech	48	26.2
Sean	Williams	FR FF	44	20
Randy	Buck	FR FF	45	22

Based off this, I am trying to have the following criteria:

if age >= 50 AND service >= 25, name is green.

If age < 50 and service >= 25, name is orange

Age > 45 and service between 20-25, yellow

Based on that, Brian smith should not change color, Michael Turner should be green, greg myers should be orange, sean williams should not change, randy buck should be yellow.

Then I'm also trying to have a slider or a "date select" that will allow me look at days in the future (if a slider it would start at current year, then fast forward up to 5 years). Having a "select date" that defaults to the current date but could put dates in the future to make the ages/years of service increase (basically use it to look into the future by adding time to the 2 columns).