cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## Calculating Retirement Date based on Multiple Criterias

Hi Guys,

I am handling a global dataset and I need to calculate retirement date. The retirement date changes from country to country and there are some other factors like employee type, gender, location which affect the retirement date. Here is an example:

Example 1 - retirement date varies based on employee type and gender

In Region Asia, For Country China, For Blue Collars, the retirement date is 50 years from Date of Birth. (DOB - 08/21/1962, DOR - 08/20/2012)

In Region Asia, For country China, For gender 'Male', For white collars,  the retirement age 60 years from Date of Birth. (DOB - 08/21/1962, DOR - 08/20/2022)

In Region Asia, For country China, For gender 'Female', For white collars,  the retirement age 55 years from Date of Birth.(DOB - 08/21/1962, DOR - 08/20/2017)

Example 2 - retirement date varies based on the employee type and location

In region Latin America, For country Brazil, For employee type 'Managers' and 'White Collars', For location 'Sao Paulo', the retirement age is 60 years from Date of Birth (DOB - 01/15/1963, DOR - 12/31/2022)

Example 3 - retirement date varies based on retirement month

In region Asia, for country Japan,the retirement age is 60 years from date of birth but if the actual retirement date falls between Jan- Jun, then end of June is considered as retirement date (DOB - 01/25/1972, DOR - 06/30/2032)

Can someone help me derive the retirement date based on the above mentioned criterias. I have herewith shared the screenshot of the data and have also attached the sample data file with comments related to each of the criteria.

@amitchandak

1 ACCEPTED SOLUTION
Super User

Don't know how you model is setup, but for the table you present you need to add the following column:

``````RetirementDate =
VAR YOB =
YEAR ( 'Table'[Date of Birth (MM/DD/YYYY)] )
VAR MOB =
MONTH ( 'Table'[Date of Birth (MM/DD/YYYY)] )
VAR DOB =
DAY ( 'Table'[Date of Birth (MM/DD/YYYY)] )
VAR YearsTOR =
LEFT ( 'Table'[Retirement Criteria ], 2 )
RETURN
SWITCH (
TRUE (),
'Table'[Country] = "Germany",
DATE ( YOB + YearsTOR, mob, DOB ) - 1,
'Table'[Country] = "Switzerland", EOMONTH ( DATE ( YOB + YearsTOR, mob, DOB ), 0 ),
'Table'[Country] = "France", DATE ( YOB + YearsTOR, 12, 31 ),
'Table'[Region] = "Latin America",
DATE ( YOB + YearsTOR, MOB, 1 ) - 1,
'Table'[Country] = "Japan",
IF ( MOB <= 6, DATE ( YOB + YearsTOR, 6, 30 ), DATE ( YOB + YearsTOR, 12, 31 ) ),
'Table'[Country] = "China",
DATE ( YOB + YearsTOR, mob, DOB ) - 1
)``````

This can also be done using a intermidiate table where you have the requiremtns you can do the following:

The criterias I have used are:

PD - Previous Day
PM - Previous Month
EOS - End of Semester
EOM - End of Month
EOY - End of Year

``````RetirementDate_L =
VAR YOB =
YEAR ( 'Table'[Date of Birth (MM/DD/YYYY)] )
VAR MOB =
MONTH ( 'Table'[Date of Birth (MM/DD/YYYY)] )
VAR DOB =
DAY ( 'Table'[Date of Birth (MM/DD/YYYY)] )
VAR YearsTOR =
LOOKUPVALUE (
'Retirement Criteria'[Retirement Criteria ],
'Retirement Criteria'[Region], 'Table'[Region],
'Retirement Criteria'[Country], 'Table'[Country],
'Retirement Criteria'[Employee Type], 'Table'[Employee Type],
'Retirement Criteria'[Gender], 'Table'[Gender],
'Retirement Criteria'[Location], 'Table'[Location]
)
VAR TYPETOR =
LOOKUPVALUE (
'Retirement Criteria'[Retirement day],
'Retirement Criteria'[Region], 'Table'[Region],
'Retirement Criteria'[Country], 'Table'[Country],
'Retirement Criteria'[Employee Type], 'Table'[Employee Type],
'Retirement Criteria'[Gender], 'Table'[Gender],
'Retirement Criteria'[Location], 'Table'[Location]
)
RETURN
SWITCH (
TYPETOR,
"PD",
DATE ( YOB + YearsTOR, mob, DOB ) - 1,
"PM",
DATE ( YOB + YearsTOR, MOB, 1 ) - 1,
"EOS",
IF ( MOB <= 6, DATE ( YOB + YearsTOR, 6, 30 ), DATE ( YOB + YearsTOR, 12, 31 ) ),
"EOM", EOMONTH ( DATE ( YOB + YearsTOR, mob, DOB ), 0 ),
"EOY", DATE ( YOB + YearsTOR, 12, 31 )
)``````

Check PBIX file attach,

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

3 REPLIES 3
Helper III

@parry2k @MattAllington @ashishmathur @MFelix   - Hi experts, can you please guide?

Super User

Don't know how you model is setup, but for the table you present you need to add the following column:

``````RetirementDate =
VAR YOB =
YEAR ( 'Table'[Date of Birth (MM/DD/YYYY)] )
VAR MOB =
MONTH ( 'Table'[Date of Birth (MM/DD/YYYY)] )
VAR DOB =
DAY ( 'Table'[Date of Birth (MM/DD/YYYY)] )
VAR YearsTOR =
LEFT ( 'Table'[Retirement Criteria ], 2 )
RETURN
SWITCH (
TRUE (),
'Table'[Country] = "Germany",
DATE ( YOB + YearsTOR, mob, DOB ) - 1,
'Table'[Country] = "Switzerland", EOMONTH ( DATE ( YOB + YearsTOR, mob, DOB ), 0 ),
'Table'[Country] = "France", DATE ( YOB + YearsTOR, 12, 31 ),
'Table'[Region] = "Latin America",
DATE ( YOB + YearsTOR, MOB, 1 ) - 1,
'Table'[Country] = "Japan",
IF ( MOB <= 6, DATE ( YOB + YearsTOR, 6, 30 ), DATE ( YOB + YearsTOR, 12, 31 ) ),
'Table'[Country] = "China",
DATE ( YOB + YearsTOR, mob, DOB ) - 1
)``````

This can also be done using a intermidiate table where you have the requiremtns you can do the following:

The criterias I have used are:

PD - Previous Day
PM - Previous Month
EOS - End of Semester
EOM - End of Month
EOY - End of Year

``````RetirementDate_L =
VAR YOB =
YEAR ( 'Table'[Date of Birth (MM/DD/YYYY)] )
VAR MOB =
MONTH ( 'Table'[Date of Birth (MM/DD/YYYY)] )
VAR DOB =
DAY ( 'Table'[Date of Birth (MM/DD/YYYY)] )
VAR YearsTOR =
LOOKUPVALUE (
'Retirement Criteria'[Retirement Criteria ],
'Retirement Criteria'[Region], 'Table'[Region],
'Retirement Criteria'[Country], 'Table'[Country],
'Retirement Criteria'[Employee Type], 'Table'[Employee Type],
'Retirement Criteria'[Gender], 'Table'[Gender],
'Retirement Criteria'[Location], 'Table'[Location]
)
VAR TYPETOR =
LOOKUPVALUE (
'Retirement Criteria'[Retirement day],
'Retirement Criteria'[Region], 'Table'[Region],
'Retirement Criteria'[Country], 'Table'[Country],
'Retirement Criteria'[Employee Type], 'Table'[Employee Type],
'Retirement Criteria'[Gender], 'Table'[Gender],
'Retirement Criteria'[Location], 'Table'[Location]
)
RETURN
SWITCH (
TYPETOR,
"PD",
DATE ( YOB + YearsTOR, mob, DOB ) - 1,
"PM",
DATE ( YOB + YearsTOR, MOB, 1 ) - 1,
"EOS",
IF ( MOB <= 6, DATE ( YOB + YearsTOR, 6, 30 ), DATE ( YOB + YearsTOR, 12, 31 ) ),
"EOM", EOMONTH ( DATE ( YOB + YearsTOR, mob, DOB ), 0 ),
"EOY", DATE ( YOB + YearsTOR, 12, 31 )
)``````

Check PBIX file attach,

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Helper III

@MFelix This worked perfectly and provides me the required details. Thank you very much!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.