Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sokatenaj
Helper III
Helper III

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!

2 ACCEPTED SOLUTIONS
MFelix
Super User
Super User

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:

 

Retirement.png

 

Regards,

MFelix

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

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] ) )
        )
    )

%Retirement.png

 

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

19 REPLIES 19
MFelix
Super User
Super User

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:

 

Retirement.png

 

Regards,

MFelix

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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: 

markramer_0-1674489129434.png

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 

Hi @markramer ,

 

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 ))

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

Hi @markramer 

 

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?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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? 

Hi @markramer 

 

Try the following

 

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

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Thanks, @MFelix. I understand. And I'm running into a similar problem with banding these results again in another calculated column.

 

Now I am trying to say, if Today is greater than retirement date then "Over Retirement", if Retirement date is within 3 years of Today then "Within 3 Years"  else, Not Eligible 

 

 I really need to take a class on DAX  but my company won't pay for it.  My reports are boss, but it's weird trying to get a handle on how different DAX is from Excel code and even SQL! I'm really good with Excel and various forms of SQL, but for some reason I'm struggling with DAX. Some stuff really makes sense, other stuff to me doesn't. 




 

 

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"
     )

 

over_under.png

 

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

Give me kudos on the replies :D:D


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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!

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.

 

Grouping.png

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

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix,

 

That makes sense, but I want to have 4 categories on the X Axis of the 100% stacked bar which is  Over retirement, within 3 years, within 5 years, and not eligible.  The within 5 years needs to also have the within 3 years data rolled into it as a cumulative sum. That's where I'm stuck. 

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] ) )
        )
    )

%Retirement.png

 

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Good God that is complicated! I really appreciate it. Thank you so much!!!!!

@MFelixYou got it!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.