Dynamically find the nearest non-blank value in Sameperiod last year

[Antmkjr]

I have data as shown below:

Expected o/p is nearest non-blank value in the same period last year. If for Sameperiod last year Value is not available,  returns a blank, it should search both before and after that month (expanding outward month-by-month) for until a closest non-blank is found

Date	Value	Expected o/p
29/12/2023	800
15/02/2024	50
28/04/2024	40
08/04/2024	80
07/01/2025	90	800
08/01/2025	50	800
13/01/2025	900	800
10/02/2025	60	50
28/02/2025	80	50
17/03/2025	150	50
15/04/2025	100	80

[lbendlin]

SAMEPERIODLASTYEAR for a single value is ambiguous. Please be more specific. Do you mean "prior year" ?

or like this

[Antmkjr]

Spot On! Just that total is incorrect.

[lbendlin]

Which one is spot on? For the total to be correct you can wrap the code in an aggregation.

[Antmkjr]

First one, but is it not already wrapped in aggregation.?

[lbendlin · Accepted solution]