Find nearest value in column greater than X (variable)

[jeyare]

Hello there,

I can't find a way to get the nearest value in a column, which is greater than X (variable). The entire evaluation is within the same table.

Details:

I need to evaluate data stored in column name: Age Index (decimal), associated with column: Weight

then I need to create two new columns:

Evaluation & Evaluated weight

for the Evaluation column:

VAR AgeIndexDiff = 1 # I will use it for for a slicer filter in next stage

Evaluation = (nearest value of the first NonBlank row in Age Index column) >= ( "current row value of Age Index" + AgeIndexDiff)

for the Evaluated weight column:

Evaluated weight = associated Weight from the Evaluation row value

Data Sample (as-is):

Age Index	Weight
7.72	15.29
7.87	15.91
8.25	17.91
8.36	17.95
8.73	18.57
8.82	20.17
9.6	20.57
9.13	22.16
9.29	22.75
9.58	25.35
9.63	26.86
9.67	27.52
9.71	27.69
9.81	29.57
9.94	30.68
9.99	31.62
10.22	32.93
10.47	33.2
10.7	36.66
11	37.68
11.22	38.21
11.47	38.53
11.63	39.54
12.15	40.59
12.23	42.38
12.42	46.22
12.56	47.74
12.95	48.21
13.2	48.52
13.51	49.05
13.7	50.39
13.84	50.44
14.9	51.19
14.31	52.59

Data to-be stage (when VAR = 1):

Age Index	Weight	Evaluation	Evaluated weight
7.72	15.29	8.73	18.57
7.87	15.91	8.82	20.17
8.25	17.91	9.29	22.75
8.36	17.95	9.29	22.75
8.73	18.57	9.71	27.69
8.82	20.17	9.81	29.57
9.6	20.57	9.99	31.62
9.13	22.16	10.22	32.93
9.29	22.75	11.22	38.21
9.58	25.35	10.47	33.2
9.63	26.86	10.7	36.66
9.67	27.52	10.7	36.66
9.71	27.69	10.7	36.66
9.81	29.57	10.7	36.66
9.94	30.68
9.99	31.62
10.22	32.93
10.47	33.2
10.7	36.66

[AllisonKennedy · Accepted solution]

@jeyare  You should be able to do this with the MINX function, however the part where you want to use a slicer won't work with a calculated column: https://excelwithallison.blogspot.com/2020/09/reporting-order-of-operations.html

In order for it to update with slicers, you'd need to create them as a MEASUREs

Evaluation =

VAR _CurrentAgeIndex = SELECTEDVALUE(DataTable[Age Index])

VAR _AgeIndexDiff = SELECTEDVALUE(SlicerTable[Diff])

RETURN

MINX(FILTER(DataTable, DataTable[Age Index] >= _CurrentAgeIndex + _AgeIndexDiff ),  DataTable[Age Index] )

Evaluated Weight =

VAR _CurrentAgeIndex = SELECTEDVALUE(DataTable[Age Index])

VAR _AgeIndexDiff = SELECTEDVALUE(SlicerTable[Diff])

VAR _Evaluation = 

MINX(FILTER(DataTable, DataTable[Age Index] >= _CurrentAgeIndex + _AgeIndexDiff ),  DataTable[Age Index] )

RETURN

MINX(FILTER(DataTable, DataTable[Age Index] = _Evaluation), DataTable[Weight] )

[AllisonKennedy · Accepted solution]

@jeyare  You should be able to do this with the MINX function, however the part where you want to use a slicer won't work with a calculated column: https://excelwithallison.blogspot.com/2020/09/reporting-order-of-operations.html

In order for it to update with slicers, you'd need to create them as a MEASUREs

Evaluation =

VAR _CurrentAgeIndex = SELECTEDVALUE(DataTable[Age Index])

VAR _AgeIndexDiff = SELECTEDVALUE(SlicerTable[Diff])

RETURN

MINX(FILTER(DataTable, DataTable[Age Index] >= _CurrentAgeIndex + _AgeIndexDiff ),  DataTable[Age Index] )

Evaluated Weight =

VAR _CurrentAgeIndex = SELECTEDVALUE(DataTable[Age Index])

VAR _AgeIndexDiff = SELECTEDVALUE(SlicerTable[Diff])

VAR _Evaluation = 

MINX(FILTER(DataTable, DataTable[Age Index] >= _CurrentAgeIndex + _AgeIndexDiff ),  DataTable[Age Index] )

RETURN

MINX(FILTER(DataTable, DataTable[Age Index] = _Evaluation), DataTable[Weight] )

[jeyare · Accepted solution]

Thx Allison,

works great with some tunes, when I calculate MIN and MAX values to find the nearest possible difference in ABS value (+ or -)