Compare a value of a table in a range of values ​​from another table

[psembaj]

Hello! 1 query with two tables. Table 1 contains 6 columns: Fecha - Mes - Anio - Desde - Hasta - Incremento Table 2 contains 6 columns. The relationship between these tables is the Mes and Anio. I need each value of the table 2 column " Cant_Real" to be validated by table 1 between the "Desde" - "Hasta" range and return the value of the "Incremento".

Example:

Table 2: "Cant_Real" = 105            //     Corresponds:      Table 1: "Desde"=105    "Hasta"=109     //  Value that I need is: "110"

The values ​​in the column "DESDE" and "HASTA" change, so they can not be static. I need to evaluate a value between a range of values ​​and bring the value that corresponds to it from another column. I do not know how to do it. Could anyone help me?

[Anonymous · Accepted solution]

What you need is the LOOKUP Function. Try this:

ValueNeeded =
LOOKUPVALUE ( Table1[Incremento], Table1[Desde], Table2[Cant_Real] )

[v-jiascu-msft · Accepted solution]

Hi @psembaj,

Please try this formula in a calculated column. 

Value =
CALCULATE (
    MIN ( 'Table1'[Incremento] ),
    FILTER (
        'Table1',
        'Table1'[DESDE] <= [Cant Real]
            && IF ( ISBLANK ( 'Table1'[HASTA] ), 99999, 'Table1'[HASTA] ) >= [Cant Real]
    )
)

Best Regards,
Dale

[psembaj]

Hello! Good day! I was testing the proposed solution, but I find that it does not work correctly. For some reason it works with certain values ​​and not with others, example:

The values ​​for the month of 10 do it correctly, but not for those of the month 9. Any suggestions, ideas, comments? I thank you because I really do not know what is happening.

Thanks

[v-jiascu-msft]

Hi @psembaj,

Just see from your snapshots, there isn't any data of month 9 in table 1. That's why the results are blanks. If this isn't the cause, please share a sample file. Please mask the sensitive parts first.

Best Regards,
Dale

[Anonymous · Accepted solution]

What you need is the LOOKUP Function. Try this:

ValueNeeded =
LOOKUPVALUE ( Table1[Incremento], Table1[Desde], Table2[Cant_Real] )