Forum Discussion
Power Query / DAX equivalent to Excel's Index Match with Dynamic Array
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 varPriceThe problem is you need a bit of modeling. See my PBIX. file. It returns this:
Here is what I did
- 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.
- I used hte Name table as my 1-many in the relationship shown here:
-
- 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.
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:
| Prod | Date | Cost |
| A | 1/jan/20 | 100 |
| A | 4/jan/20 | 150 |
| A | 8/jan/20 | 90 |
Usage table
| Prod | Date | Cost |
| A | 2/jan/20 | 100 |
| A | 3/jan/20 | 100 |
| A | 5/jan/20 | 150 |
| A | 6/jan/20 | 150 |
| A | 8/jan/20 | 90 |
| A | 9/jan/20 | 90 |
Thank you again, in the attached workbook I've put how it works in Excel
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
varPriceThe problem is you need a bit of modeling. See my PBIX. file. It returns this:
Here is what I did
- 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.
- I used hte Name table as my 1-many in the relationship shown here:
-
- 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.
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