closer date to a fixed date but the date some years is not included

[carenel]

Hello, I am learning Power bi, I want a new column with the date of 19/11 of every year, but in the row DATE sometimes there is not a date for 19/11 for every year, thus it has to search for the nearer date.

Many thanks

DATE	fuel		result: NEAREST DATE 19/11 OF EVERY YEAR
15/11/2001	0,2894		19/11/2001
16/11/2001	0,8148		19/11/2001
17/11/2001	0,1862		19/11/2001
18/11/2001	0,5821		19/11/2001
19/11/2001	0,7754		19/11/2001
…...
15/11/2003	0,4618		18/11/2003
16/11/2003	0,4720		18/11/2003
17/11/2003	0,5426		18/11/2003
18/11/2003	0,5324		18/11/2003
20/11/2003	0,4011		18/11/2003
…...
15/11/2004	0,6011		16/11/2004
16/11/2004	0,3605		16/11/2004
20/11/2004	0,2667		16/11/2004

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

=CALCULATE(MAX(Data[DATE]),FILTER(Data,Data[Year]=EARLIER(Data[Year])&&Data[DATE]<=DATE(Data[Year],11,19)))

Hope this helps.

[carenel]

many thanks Ashish very elegant solution

[Ashish_Mathur]

You are welcome.  Thank you for yoru kind words.