Forum Discussion
Help Creating Retirement Date Dax Measure
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.
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
5 Replies
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
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?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
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?
