Closest lesser

[Den-iska]

Sorry for my English, first.
I have a Column (Integer), lets say Column [A], numbers in rows are unique.

Seems that all I need is to create another Column [B] and put there numbers which are taken from Column [A], but are closest lesser than numbers in Column [A] in respected rows.

[Den-iska]

 I have a column like this

And i need to add column like this:

So latest row in Column B is 12 because 12 is closest lesser to 13 which is in the same row in Column A. But on one row abowe there is a 10, because there is no 11 in Column A.
(images are from Excel, but I need it to be done in DAX).
Thanks.

[Anonymous · Accepted solution]

Hi @Den-iska ,

Refer this formula.

Column = CALCULATE(MAX('Table'[Column1]),FILTER('Table','Table'[Column1]<EARLIER('Table'[Column1])))

Best Regards,

Jay

[amitchandak]

@Den-iska , I am not clear with the need.

See if new column can help

new column =
var _min = maxx(filter(Table, [columnA] < earlier([columnA])),[columnA])
var _max = minx(filter(Table, [columnA] > earlier([columnA])),[columnA])
return
if(_max -[columnA] > [columnA] - _min , _min, _max)

OR

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.