Calculating Retirement Date based on Multiple Criterias

[adhumal2]

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.

https://drive.google.com/file/d/1jeF6741nisZBgrSlgQl7IHZBcGW0N6or/view?usp=sharing 

@amitchandak 

[adhumal2]

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

[MFelix · Accepted solution]

Hi @adhumal2 ,

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

Now add the following column to your original table:

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,

[adhumal2]

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