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

MFelix's avatar
MFelix
Super User
9 years ago

Hi sokatenaj,

 

Add the following formula:

Over/Under = 
     SWITCH (
         TRUE (),
         TODAY () > People[Retirement Date], "Over retirement",
         People[Retirement Date]
             <= DATE (( YEAR ( TODAY () ) + 3), MONTH ( TODAY () ), DAY (      TODAY () ) ), "Within 3 Years",
         "Not Eligible"
     )

 

 

 

Regards,

MFelix

sokatenaj's avatar
sokatenaj
Advocate II
9 years ago

You're a gem, MFelix! I wish I could give you something to show my appreciation! Thank you so much!

  • MFelix's avatar
    MFelix
    Super User
    9 years ago

    Give me kudos on the replies :D:D

    • sokatenaj's avatar
      sokatenaj
      Advocate II
      9 years ago

      Hi MFelix,

       

      Not sure if you can assist here. I appreciate the help with the banding. However, I ran into a problem. I am trying to do a 100% stacked bar chart but realized that the "Within 5 years" retirement number is not factoring in the people that are also within 3 years of retirement. 

       

      For example, I have a headcount of 6000.  Over Retirement is 280, Within 3 years is 200, within 5 years is 310, and not eligible is 5210. For a value, I would just use the calculated column that you helped me create which is "Retirement Eligibility" but for the value of 5 years, it only puts 310 (Within 5 years), not the sum of 200 + 310.  What can I do here to make this happen? I can only add 1 value to the VALUE field. Just not sure how to go about it...

       

      Any help would be great. Please and thank you!

      • MFelix's avatar
        MFelix
        Super User
        9 years ago

        Hi sokatenaj, 

         

        Create a group on the new group level on your table with this column you will then be able to do your charts based on that.

         

        I have simulated this on the example I have in my computer and the result is below.

         

        As you can see there is a new "field" in your table with 2 squares crossing each other representing the groups.

         

        This is the fastest and easiest way, be aware that if you create a new category you need to edit you grouping as a best practice I always tick the box Include Other Group in the bottom of the group options that way the ungroup values show in my graph and I can have a "check-list".

         

        Doing it with measures is also possible but make it a little bit more complex in terms of dax but possible, but believe this is much better for your purpose.

         

        Regards,

        MFelix