cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

LOOK UP VALUE W/O RELATIONSHIP

Dear community,

 

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

 

INVENTORY.PNG

 

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!

 

 

 

 

3 REPLIES 3
TomMartens
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

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!

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:

image.png

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

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.