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, b...
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

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 ))
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    It is not a measure/metric, it is a regular calculated column, added by "New Column" in the Data area.

    • MFelix's avatar
      MFelix
      Super User
      3 years ago

      Hi Anonymous 

       

      Truy the following code:

      Retirement date = 

DATE(YEAR(Employee[Birthday])+ 67, Month(Employee[Birthday]), DAY(Employee[Birthday]))

      This seems similar to your but has you can see there is no indication of the year and after the date. If this still gets you the same error can you please share a mockup of your data?

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        That did the job, however I found the error to be within the data.

        Birthday had some blank/null values and thus I got the error. 

        My workaround was to replace null values inside birthday with 01.01.1900 as placeholder, but I don't really like this kind of problem solving. I tried with IF and ISBLANK but could not find a way. Do you maybe have a solution for me that does the birthday + 67 if there is a valid birthday for that row and else just output null for retirement date?