Match Item Prices over Time (with lookupvalue)?

[Brotedo]

I have two tables, a FactSales table and an ItemPrice table. The FactSales table has an item number and an invoice date. The ItemPrice table has an item number, an effective date, an expiration date, and a price (so the item number is not unique in this table - an item has multiple entries for prices at different times). I want to pull the price of the item at the time of the invoice into my FactSales table.

I tried to embed a lookupvalue in a calculate with filters on the ItemPrice table based on eff. and exp. date, but I realized I couldn't actually use lookupvalue inside calculate. I'm stuck on this one.

Table examples:

ItemNumber	Eff. Date	Exp. Date	Price
1A	1/1/2020	1/1/2021	$10
1A	1/1/2021		$12

ItemNumber	InvoiceDate	Price (What I want)
1A	6/3/2020	$10
1A	4/1/2021	$12

[Ashish_Mathur]

Hi,

In the Query Editor, insert Today's data in place of blanks in the Exp. Date column.  In Table2, write this calculated column formula

Price = calculate(SUM('Table1'[Price]),filter('Table1','Table1'[Itemnumber]=earlier('Table2'[Itemnumber])&&'Table1'[Eff. Date]<=earlier('Table2'[Date])&&'Table1'[Exp. Date]>earlier('Table2'[Date])))

[Anonymous · Accepted solution]

Hi @Brotedo ,

You can create a calculated column in the table 'FactSales' as below to get it, please find the details in the attachment.

Price = 
CALCULATE (
    MAX ( 'ItemPrice'[Price] ),
    FILTER (
        'ItemPrice',
        'ItemPrice'[ItemNumber] = 'FactSales'[ItemNumber]
            && 'FactSales'[InvoiceDate] >= 'ItemPrice'[Eff. Date]
            && 'FactSales'[InvoiceDate]
                <= IF (
                    ISBLANK ( 'ItemPrice'[Exp. Date] ),
                    DATE ( 9999, 12, 31 ),
                    'ItemPrice'[Exp. Date]
                )
    )
)

Best Regards