Forum Discussion

sokatenaj's avatar
sokatenaj
Advocate II
9 years ago
Solved

Calculating Retirement Date

Good day, 

 

Similar to my earlier post about generational bands. I want to create a retirement date calculation based on certain age bands as a calculated column. I know how to do this in excel, but not in the DAX formulation. 

 

For example, if you were born 1937-1942 your retirement age is 65. So what is that future calculated date based on your birth date based on the various birthdates in the table in a particular column? If you were born 1943-1959 your retirement age is 66 and then what is that date based on your birthdate, then finally if you were born 1960 or later your retirement age is 67 and what is your retirement date? I want to try to compile these as one calculated column because then I can then calculate a DATE DIFF between the birth date and retirement date as another calculated column to have it write "Over Retirement, Within 3 Years, etc" based on "TODAY()" 

 

Please and thank you. I'm surprised at how some formulas I am doing really well and then others I am just over thinking!

Need Help
  • MFelix's avatar
    MFelix
    9 years ago

    Hi sokatenaj,

     

    Add a column to your table with the following sintax:

     

    Retirement Date =
SWITCH (
    TRUE (),
    YEAR ( People[Birth-Date] ) >= 1937
        && YEAR ( People[Birth-Date] ) <= 1942,
    (
        DATE ( ( YEAR ( [Birth-Date] ) + 65 ), MONTH ( People[Birth-Date] ), DAY ( People[Birth-Date] ) )
    ), YEAR ( People[Birth-Date] ) >= 1942
        && YEAR ( People[Birth-Date] ) <= 1959,
    (
        DATE ( ( YEAR ( [Birth-Date] ) + 66 ), MONTH ( People[Birth-Date] ), DAY ( People[Birth-Date] ) )
    ),
    (
        DATE ( ( YEAR ( [Birth-Date] ) + 67 ), MONTH ( People[Birth-Date] ), DAY ( People[Birth-Date] ) )
    )
)

    This will give the following result:

     

     

    Regards,

    MFelix

     

     

  • MFelix's avatar
    MFelix
    9 years ago

    Hi sokatenaj,

     

    Creae this measure:

     

    Over_Under_ = 
		VAR within_5 =
			CALCULATE (
				COUNT ( People[OVer/Under] ),
				People[OVer/Under] = "Within 5 Years"
			)
		VAR within_3 =
			CALCULATE (
				COUNT ( People[OVer/Under] ),
				People[OVer/Under] = "Within 3 Years"
			)
		RETURN
			IF (
				VALUES ( People[OVer/Under] ) = "Within 5 years",
				within_5 + within_3,
				COUNT ( People[OVer/Under] )
			)

    Then add it to your graph, making this in this way you will be double counting the within 3 years and change the way you are calculating the 100% because instead of having 18 names you will get 21.

     

    I have made this new formula that calculates the percentages acummulated but in this way your chart will be above 100%:

     

    Over_Under_% =
VAR within_5 =
    DIVIDE (
        CALCULATE (
            COUNT ( People[OVer/Under] ),
            People[OVer/Under] = "Within 5 Years"
        ),
        CALCULATE ( COUNT ( People[OVer/Under] ), ALLSELECTED ( People[OVer/Under] ) )
    )
VAR within_3 =
    DIVIDE (
        CALCULATE (
            COUNT ( People[OVer/Under] ),
            People[OVer/Under] = "Within 3 Years"
        ),
        CALCULATE ( COUNT ( People[OVer/Under] ), ALLSELECTED ( People[OVer/Under] ) )
    )
RETURN
    IF (
        VALUES ( People[OVer/Under] ) = "Within 5 years",
        within_5 + within_3,
        DIVIDE (
            CALCULATE ( COUNT ( People[OVer/Under] ) ),
            CALCULATE ( COUNT ( People[OVer/Under] ), ALLSELECTED ( People[OVer/Under] ) )
        )
    )

     

    to me these to totals don't make sense to be presented in the same chart because as said before you will be double counting the within 3 years values.

     

    Please tell me if I can help in anything else.

     

    Regards,

    MFelix

19 Replies

  • MFelix's avatar
    MFelix
    Super User
    9 years ago

    Hi sokatenaj,

     

    Add a column to your table with the following sintax:

     

    Retirement Date =
SWITCH (
    TRUE (),
    YEAR ( People[Birth-Date] ) >= 1937
        && YEAR ( People[Birth-Date] ) <= 1942,
    (
        DATE ( ( YEAR ( [Birth-Date] ) + 65 ), MONTH ( People[Birth-Date] ), DAY ( People[Birth-Date] ) )
    ), YEAR ( People[Birth-Date] ) >= 1942
        && YEAR ( People[Birth-Date] ) <= 1959,
    (
        DATE ( ( YEAR ( [Birth-Date] ) + 66 ), MONTH ( People[Birth-Date] ), DAY ( People[Birth-Date] ) )
    ),
    (
        DATE ( ( YEAR ( [Birth-Date] ) + 67 ), MONTH ( People[Birth-Date] ), DAY ( People[Birth-Date] ) )
    )
)

    This will give the following result:

     

     

    Regards,

    MFelix

     

     

    • sokatenaj's avatar
      sokatenaj
      Advocate II
      9 years ago

      This is fabulous! This was a little more complex than I thought it would be in DAX. Thanks so much!!

      • MFelix's avatar
        MFelix
        Super User
        9 years ago

        Hi sokatenaj,

         

        The formula is a little bit more complex because you want to return a date and in that way you need to add each of the date fields in you final result, if you only wanted to return the year it would be simpler.

         

        Regards,

        MFelix

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hey, I know this post is pretty old but I am working on a similar topic. 
      My task is a little less complex as I use 67 years for every employee, but I get an error which I cannot resolve: 

      Is this due the syntax in DAX/PBI has changed? My Birthday column is a date value, e.g. 25.11.1966.

      Thanks for any help.

      MFelix 

      • MFelix's avatar
        MFelix
        Super User
        3 years ago

        Hi Anonymous ,

         

        Is this a metric or a calculated column? If this is a metric you need to use a MAX or a MIN function in your code before getting the date part for example something similar to this:

        Retirement date = 

VAR BirthdayDate = SELECTECEDVALUE(Employee[Birthday])
Return
DATE(YEAR(BirthdayDate)+ 67, Month(BirthdayDate), DAY(BirthdayDate ))