Forum Discussion

emcalleja's avatar
emcalleja
Regular Visitor
6 years ago
Solved

Power Query / DAX equivalent to Excel's Index Match with Dynamic Array

Hi all I hope you all are safe and sound.   Hope you guys can help me, I've been struggling with this problem for a few days now.   I have two tables to handle raw material, one for purchases (En...
  • edhans's avatar
    edhans
    6 years ago

    Ok emcalleja , try this. And I apologize if I mangle the field meanings due to them being in not English. ğŸ˜‚

    This measure works I think.

    Price = 
VAR varCurrentDate =
    MAX( Salidas[Fecha de movimiento] )
VAR varCurrentItem =
    MAX( Salidas[Nombre] )
VAR varEntradasData =
    FILTER(
        Entradas,
        Entradas[Nombre] = varCurrentItem
            && Entradas[Fecha de movimiento] <= varCurrentDate
    )
VAR varEntradasMaxDate =
    MAXX(
        varEntradasData,
        [Fecha de movimiento]
    )
VAR varPrice =
    MAXX(
        FILTER(
            varEntradasData,
            [Fecha de movimiento] = varEntradasMaxDate
        ),
        [Costo / Venta]
    )
RETURN
    varPrice

    The problem is you need a bit of modeling. See my PBIX. file. It returns this:

    Here is what I did

    1. I got all of the names (nombres?) from both tables and put them in a Names table, then removed duplicates. The problem is you have some case sensitivity issues, so I had to change case to "proper" because to Power Query, Car <> CAR <> CaR. DAX doesn't care. Power Query does.
    2. I used hte Name table as my 1-many in the relationship shown here:

      1.  

    3. Then I created and used that measure in the table visual above. I am pretty sure this can be optimized, but shoudl get you started.

     

     

  • emcalleja's avatar
    emcalleja
    Regular Visitor
    6 years ago

    Hi!

     

    Thank you for the fast response.

     

    The thing is that when I merge the Queries I get only the values that match exactly the given date:

     

     

    And that of course is not exactly what I want, what I want is to take "the cost of the latest purchase before the date that I used the product".

     

    What Excel does is that goes through the table and makes an approximate match, and I don't know how to acomplish this with Power Query or Dax!

     

    Here's the file with the sample data:

     

    https://www.dropbox.com/s/a45k8ej1utglahg/pq_cost.xlsx?dl=0

     

    The expected result should look like this:

     

    Purchase table:

    ProdDateCost
    A1/jan/20100
    A4/jan/20

    150

    A8/jan/20

    90

     

    Usage table

    ProdDateCost
    A2/jan/20100
    A3/jan/20

    100

    A5/jan/20

    150

    A6/jan/20

    150

    A8/jan/20

    90

    A9/jan/20

    90

     

    Thank you again, in the attached workbook I've put how it works in Excel

     

     

     

    • edhans's avatar
      edhans
      Community Champion
      6 years ago

      Ok emcalleja , try this. And I apologize if I mangle the field meanings due to them being in not English. ğŸ˜‚

      This measure works I think.

      Price = 
VAR varCurrentDate =
    MAX( Salidas[Fecha de movimiento] )
VAR varCurrentItem =
    MAX( Salidas[Nombre] )
VAR varEntradasData =
    FILTER(
        Entradas,
        Entradas[Nombre] = varCurrentItem
            && Entradas[Fecha de movimiento] <= varCurrentDate
    )
VAR varEntradasMaxDate =
    MAXX(
        varEntradasData,
        [Fecha de movimiento]
    )
VAR varPrice =
    MAXX(
        FILTER(
            varEntradasData,
            [Fecha de movimiento] = varEntradasMaxDate
        ),
        [Costo / Venta]
    )
RETURN
    varPrice

      The problem is you need a bit of modeling. See my PBIX. file. It returns this:

      Here is what I did

      1. I got all of the names (nombres?) from both tables and put them in a Names table, then removed duplicates. The problem is you have some case sensitivity issues, so I had to change case to "proper" because to Power Query, Car <> CAR <> CaR. DAX doesn't care. Power Query does.
      2. I used hte Name table as my 1-many in the relationship shown here:

        1.  

      3. Then I created and used that measure in the table visual above. I am pretty sure this can be optimized, but shoudl get you started.

       

       

      • emcalleja's avatar
        emcalleja
        Regular Visitor
        6 years ago

        This works perfectly, thank you for the help and with the work on understanding the values in Spanish!

         

        Thank you again.

         

        I'll work on this code to optimize it