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

[emcalleja]

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 (Entries) and one for usage. I want to reference the latest cost of purchase of a material in each occurrence of the second table (usage).

I can handle this query in Excel with the next formula:

(Within the usage table)

=INDEX(Entries[Cost],MATCH([@[MovementDate]],IF(Entries[MaterialName]=[@MaterialName],Entries[MovementDate]),1))

And this formula works perfectly because it gives me the costs of purchase of that day or the one for the latest purchase for each occurrence. The thing is that I want to input those values into a Data Model and interact with them with DAX.

Can anyone please guide me through?

Thank you in advance.

[Greg_Deckler]

@emcalleja - See if this helps: https://community.powerbi.com/t5/Community-Blog/Excel-to-DAX-Translation/ba-p/1060991

Otherwise sample data and expected result would help. Please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[edhans]

If I understand your request, this is pretty simple with a Join in Power Query on two fields on your date and material name fields. You can see more here in a walkthrough of how that works. If you need more assistance, please provide sample data.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[emcalleja]

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

[edhans · Accepted solution]

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]

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

[edhans]

Great @emcalleja - glad to assist. 

If you follow my code logic, I am just building variables and filtering tables in memory until I get to the final result. My code tends to be more verbose than necessary to help understanding. Mine in particular. 😉