Help Creating Retirement Date Dax Measure

[Kenaurec]

Hello,
 

 I'm looking to get some help to craft  two measures that determine the date  an individual will meet specific retirement requirements. I'm looking to highlight when an individual will have a combined service and age of 90, and finally  what date they will be over the age of 60 with at least 20 years of service.
 

 I plan to use the data in a Table visualization or something like the below.
(DOB = Date of Birth)

 My measure for determining the date for the 90 factor  is.

90 factor Measure =
 

Var Calculation = (90-([CurrentAge]+[CurrentServiceYears]))/2
Var Result =  Dateadd( [Todaysdate],Calculation,Year)
Return
Result

The measure I have for the 60/20 rule isn't working as I tried to use an If statement to determine if the person was over 60 before triggering but I quickly got lost in the logic of how to do it for future dates. I was trying to filter two date tables, one to produce a date for age 60 and the other the date of 20 years of service. I then tried to  filter each table down to a common date but it didn’t  work correctly -  sometimes the overlapping date I created was way too early.  

 For a model, I have an employee information table with the Name, ID tag, Date of Birth and Service start date for each individual. I have also linked this information to a date dimension table spanning the years (1950-2100) .  The frustrating part is that I know I have the information needed to tackle the issue… I just don’t know how to implement the logic for it.

 Any help would be appreciated - even just being pointed at  a resource that could help me tackle this problem.

[Ayesha_Shabir]

if we have date of joining,date of retirement, date of birth all these column and we have to find how many years left for retirement?

[dedelman_clng · Accepted solution]

Hi @Kenaurec -

You can try something like FIRSTNONBLANKVALUE. Assuming your Date table has dates way far in the future, something like this:

60-20 Date = 
FIRSTNONBLANKVALUE (
    Date3[Date],
    VAR __SvcTime =
        DATEDIFF ( Employee[ServiceStartDate], MIN(Date3[Date]),  DAY ) / 365
    VAR __Age =
        DATEDIFF ( Employee[DOB], MIN(Date3[Date]), DAY ) / 365
    RETURN
        IF ( __Age >= 60 && __SvcTime >= 20, MIN(Date3[Date]))
)

Hope this helps

David

[Kenaurec]

Hello David,

Thanks for the response.
 
The measure you've provided is returning a blank value so I'm not sure if I've done it right or if I missed  something.....   If I understand  your formula correctly, should I be using MIN Date? Would this not return the earliest date I have in my date table? I tried storing today's date value in the measure and replacing that as well but that didnt seem to make a difference.

What would cause the values to be blank in the table?

[dedelman_clng]

Hi @Kenaurec - the code I gave was for a calculated column, not a measure. That could be why you are seeing blank values.

Also, there can be no active relationship between the Date table and the Employee table or it will return blanks.

FIRSTNONBLANKVALUE iterates over the Date table, but you must use MIN() to treat each iteration as its own row, allowing you to do the calculation on a single value of Date.

Hope this helps

David

[Kenaurec]

 Right on - I  wasn't paying attention to the formula to realize that it had column references built in. 

Thanks for the help - it's working now and it probably makes more sense for me to do the rest of the calculations as columns as well.