Finding Date Closest to Another Given Month & Day

[QlikBusinessUser]

Hi,

I have a problem where I need to 'snap' a change date back from the system date to the contract date.

E.g. the change may occur on 14/04/2023 but contract is 01/01/2023 so I need to use 01/01/2023.

The issue is that the contract date moves over time each year and I need to go back in time, so I just need to use the month and day from the contract date (it's the same each year) but snap back to the first occurrence of this date in the past.

E.g. the contract date could be 01/01/2025 but my row has 14/04/2023 so I need to output 01/01/2023.

I cannot just use the month and date along with the current year as it could cover two different years.

e.g. change date of 13/01/2023 and contract of 30/11/2025 needs to return 30/11/2022. 

I need to do this in load editor.

Thanks!

[Anil_Babu_Samineni]

@QlikBusinessUser Do you have sample data set where it determine the issue to demostrate? 

[LRuCelver]

Hope it helps:

Data:
NoConcatenate Load
	ChangeDate,
    ContractDate,
    ExpectedDate,
    If(ContructedDate > ChangeDate, AddYears(ContructedDate, -1), ContructedDate) as SnappedDate;
Load
	*,
    MakeDate(Year(ChangeDate), Month(ContractDate), Day(ContractDate)) as ContructedDate;
Load
	Date(Date#(ChangeDate, 'DD/MM/YYYY')) as ChangeDate,
	Date(Date#(ContractDate, 'DD/MM/YYYY')) as ContractDate,
	Date(Date#(ExpectedDate, 'DD/MM/YYYY')) as ExpectedDate
Inline [
	ChangeDate,	ContractDate,	ExpectedDate
    14/04/2023,	01/01/2023,		01/01/2023
    14/04/2023,	01/01/2025,		01/01/2023
    13/01/2023,	30/11/2025,		30/11/2022
];