LOOK UP VALUE W/O RELATIONSHIP

[Anonymous]

Dear community,

I have to tables: "DATA" and "INVENTORY", that look moreless like the following:

What I would like to do, is look up for the combination of date, SAP and SKU in the inventory table and bring to my table what it appears on DOH. In case that combination does not exist, look for date-1, SAP and SKU. It can be done in different columns or in the same one. 

Hope you guys can help me out!

[TomMartens]

Hey,

what do you want to happen if there are more than one row in the table 'INVENTORY'?

Please create sample data using an xlsx, upload the xlsx to onedrive or dropbox and share the link.

Regards,
Tom

[Anonymous]

Hey Tom,

Concatenate values on inventory are unique. There is no more than one combination between date, sku and SAP.

There it is my example data: https://www.dropbox.com/s/c3oyhrjv9pcya12/EXAMPLE.xlsx?dl=0

Also, I attach the formula I have on Excel so it gives you and idea of what I want to achieve on Power Bi.

=IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(VLOOKUP(I2&"_"&K2&"_"&W2,Inventarios!A:D,4,0),VLOOKUP((I2-1)&"_"&K2&"_"&W2,Inventarios!A:D,4,0)),VLOOKUP((I2-2)&"_"&K2&"_"&W2,Inventarios!A:D,4,0)),VLOOKUP((I2-3)&"_"&K2&"_"&W2,Inventarios!A:D,4,0)),VLOOKUP((I2-4)&"_"&K2&"_"&W2,Inventarios!A:D,4,0)),VLOOKUP((I2-5)&"_"&K2&"_"&W2,Inventarios!A:D,4,0))

The logic is look in inventory on the date I have in data, if not found look for it in the day before and so on for 5 days. If not found, "99". Was there inventory in that exact date in that exact store I have on data? DOH = 1 --> Existing inventory.

Hope this makes it easier to understand what I am trying to do. 

PS. Sorry english is not my native language.

Thanks in advance!

[TomMartens]

Hey,

this is the DAX statement to create a calculated column in the DATA table:

Column = 
var thisDate = 'DATA'[Date]
var thisSAP = 'DATA'[SAP]
var thisSKU = 'DATA'[SKU]
var thatMAXDateInventory = 
CALCULATE(
    MAX('INVENTORY'[DATE])
    ,FILTER(
        'INVENTORY'
        ,'INVENTORY'[SAP] = thisSAP 
        && 'INVENTORY'[SKU] = thisSKU 
        && 'INVENTORY'[DATE] <= thisDate
    )
)
return
IF(
    ISBLANK(thatMAXDateInventory)
        ,99
        ,   IF(DATEDIFF(thisDate , thatMAXDateInventory , DAY) >= 5
            , 99
            , LOOKUPVALUE(
                'INVENTORY'[DOH]
                ,INVENTORY[SAP] , thisSAP
                , 'INVENTORY'[SKU] , thisSKU
                , 'INVENTORY'[DATE] ,  thatMAXDateInventory
            )
        )
)

But from the sample data, the formula above returns just this:

There is just one row that does not return 99.

Please check the sample data, as there is no valid sample data for the combination of

• SAP = CRV001
• SKU = 246589

as the dates in the INVENTORY table are always larger but not smaller (" ... if not found look for it in the day before and so on for 5 days ...") almost everytime the DAX statement returns 99.  The rule above is covered by the DAX statement that looks for the largest date of INVENTORY smaller or equal to the date of DATA.
Later on there is check if the date is not more than 5 days past

Please provide some hints if I misunderstood something wrong.

Regards,

Tom