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

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? 

MFelix's avatar
MFelix
Super User
3 years ago

Hi Anonymous 

 

Try the following

 

Retirement date = 
IF( Employee[Birthday] <> Blank(),
DATE(YEAR(Employee[Birthday])+ 67, Month(Employee[Birthday]), DAY(Employee[Birthday])))