Lookupvalue with value with exact match and date if statement

[Lookup_Help123]

Hi All,

I am having some issues connecting my unit price from my wholesale database to my orders database. What I am trying to do is pull the correct unit price for the order date. For example, I make an wholesale order on 1 June 2022 for 600 Units at $1,200, this means my unit price till I sell out the 600 units is $2. Then I have another wholesale order I make on 1 July 2022 for 300 units for $450, but I want this new lower $1.5 unit price to be mapped to the product after 600 units have been sold. See below for example dataset:

ORDERS (Sold)

Date	Product	Units	Cumulative Units	COLUMN NEEDED
10/06/2022	LEMONS	400	400	2
11/07/2022	LEMONS	100	500	2
15/07/2022	LEMONS	50	550	2
15/06/2022	PEARS	10	10	1.5
18/06/2022	PEARS	90	100	1.5
19/06/2022	PEARS	100	200	1.5
30/06/2022	PEARS	200	400	1.25
19/07/2022	LEMONS	50	600	2
20/07/2022	LEMONS	100	700	1.5

WHOLESALE ORDERS:

Date	Product	Units	Total Cost	Unit Price
01/06/2022	LEMONS	600	1200	2
01/07/2022	LEMONS	300	450	1.5
05/06/2022	PEARS	200	300	1.5
16/06/2022	PEARS	400	500	1.25

I hope the above is clear, please feel free to ask any questions you have. The idea is I have a table with all my wholesale orders with the date of purchase, product and unit price and I just want to get the Unit Price to be correctly mapped to the orders. As my business grows my unit costs fall so I would like to have the correct unit cost along side the orders. 

I have searched everywhere but have been unable to solve the issue, any and all assistance is greatly appreciated!

[Anonymous]

Hi @Lookup_Help123,

You can try to use the following calculate column formula to lookup value form the table based on nearest date and product value:

formula = 
VAR nearestDate=
    CALCULATE (
        MAX ( T2[Date] ),
        FILTER (
            T2,
            [Product] = EARLIER ( T1[Product] )
                && [Date] <= EARLIER ( T1[Date] )
        )
    )
RETURN
    LOOKUPVALUE ( T2[Unit Price], T2[Date], nearestDate, T2[Product], [Product] )

Regards,

Xiaoxin Sheng

[Lookup_Help123]

Hi @Anonymous 

Thank you for your attempt but this does not respect the number of orders for example:

I ordered 200 PEARS on 05/06/2022 and then I ordered 400 PEARS on 16/06/2022

I finished selling the 200 PEARS on 19/06/2022, this means we would need the original unit price from the 05/06/2022 order to be held till the next date.

See below:
ORDERS

Date	Product	Units	Cumulative Units Sold	COLUMN NEEDED (Unit Cost)
15/06/2022	PEARS	10	10	1.5
18/06/2022	PEARS	90	100	1.5
19/06/2022	PEARS	100	200	1.5
30/06/2022	PEARS	200	400	1.25

Wholesale Orders

Date	Product	Units	Price	Unit Price
05/06/2022	PEARS	200	300	1.5
16/06/2022	PEARS	400	500	1.25

[Anonymous]

HI @Lookup_Help123,

So you mean these calculations also need to compare with the 'table 2' units with 'table 1' cumulative units? 

After I check and test with these conditions, I found it required some recursion calculations that power bi dax formula does not supported.

The current date may not directly used in calculation, you need to iterate the table records and compare with units and cumulative unit to find out the real start and end date range of each order.(it means you need to manually calculate and list each order start end date in the variables in Dax formula, then you can use current date to check with variable return the pre-calculate/pre-defined results)

The Grge's blog about recursion calculation research:

Previous Value (“Recursion”) in DAX – Greg Deckler

Regards,

Xiaoxin Sheng

[lookup_help1234]

Hi @Anonymous ,

I see what you mean but what if I have the cumulative count on the wholesale table too? For example, now data is the following below. Can we not use another set of variable parameters in the lookuptable? 

ORDERS

Date	Product	Units	Cumulative Units Sold	COLUMN NEEDED (Unit Cost)
15/06/2022	PEARS	10	10	1.5
18/06/2022	PEARS	90	100	1.5
19/06/2022	PEARS	100	200	1.5
30/06/2022	PEARS	200	400	1.25

Wholesale Orders

Date	Product	Units	Cumulative Units Ordered	Price	Unit Price
05/06/2022	PEARS	200	200	300	1.5
16/06/2022	PEARS	400	600	500	1.25

The idea I am having is match product, match date and match cumulative orders, this should be able to fix the issue right?

Any and all help is greatly appreciated! Thank you sooo much so far too!

[Lookup_Help123]

Hi @Anonymous 

Please let me know if anything is unclear, I would like the code to respect the units, date and product. This is why I also have a cumulative Order column for the code to leverage if needed, really appreciate your assistance!

[Lookup_Help123]

Hi Xiaoxin,

Thank you for your attempt but this does not respect the number of orders for example:

I ordered 200 PEARS on 05/06/2022 and then I ordered 400 PEARS on 16/06/2022

I finished selling the 200 PEARS on 19/06/2022, this means we would need the original unit price from the 05/06/2022 order to be held till the next date.

See below:
ORDERS

Date	Product	Units	Cumulative Units Sold	COLUMN NEEDED (Unit Cost)
15/06/2022	PEARS	10	10	1.5
18/06/2022	PEARS	90	100	1.5
19/06/2022	PEARS	100	200	1.5
30/06/2022	PEARS	200	400	1.25

Wholesale Orders

Date	Product	Units	Price	Unit Price
05/06/2022	PEARS	200	300	1.5
16/06/2022	PEARS	400	500	1.25