Forum Discussion
Calculating Retirement Date
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
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
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
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
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.
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.
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 ))It is not a measure/metric, it is a regular calculated column, added by "New Column" in the Data area.
