Problem with DAX Formula

[Netdog2001ta]

Hi, I'm a Newbie in Power Bi and I'm trying to write a DAX formula to retrieve a date and a price from two tables. The first table has this structure:

ArticoloEtichetta	Descrizione	Qta	DataProtocollo	TipoDocumento	Acquisto Netto
Product_code	Description	Quantity	Date of movement	Type of ducument	Purchase price
022512	Acqua Distillata	2	01/01/2024	DDT

The first row contain the table headers, the second row contains english translation of table headers, the third row contains sample data.

The second table has this structure:

ArticoloEtichetta	Descrizione	Prezzo Netto	Data
Product_code	Description	Purchase price	Date of movement
022512	Acqua Distillata	2,00	01/01/2024

This two tyables does not have any relationship and I need to retrieve, for every row of first table, the Purchase price of the second table based on the date of first table. 
The problem is that the dates of first and second table deasn't match(in a few cases yes) and the logic is that based on the date of first table i need to retrieve the same or the previous date of the movement based on Product_code and put the Purchase price of the second table on the Purchase_Price column in the first table.

Searching the Internet and asking various IA I've tried a lot of formulas and the last is:

Acquisto Netto Corrispondente =

VAR DataSelezionata = MAX(documentitestata[DataProtocollo])

VAR DataPrecedente =

    CALCULATE(

        MAX(Storico_Test_1[Data]),

        FILTER(

            ALL(Storico_Test_1),

            Storico_Test_1[Data] <= DataSelezionata

        )

    )

VAR PrezzoCorrispondente =

    LOOKUPVALUE(

        Storico_Test_1[Acquisto Netto],

        Storico_Test_1[Data], DataPrecedente,

        BLANK()

    )

RETURN

    PrezzoCorrispondente

That formula returns no data.
I hope I've explained correctly the problem, but my english is not good.

Anyone has suggestions?

Thanks in advance for the support.

[Netdog2001ta]

Hi @mark_endicott , before of all Thanks for the support, I've changed the tables references and created the calculated column but it gives me an error that I don't understand:

A table of multiple values was specified when a single value was expected.

The lookuptable is a historic recording of price changes of products and in the same date can be multiple product price changes.
The base table is a document tables with purchase and selling values(that i filter in Power Bi).

Here is a screenshot:

Only for yolur information the BaseTable Date can be retrieved from another table related to basetable.

Here is the code adapted to my work:

Purchase Price = 
VAR _date = documentidettaglio[DataProtocollo]
VAR _prod_code = documentidettaglio[ArticoloEtichetta]
VAR prev_date_table =
    CALCULATETABLE (
        ADDCOLUMNS ( Storico_Test_1, "max_date", MAX ( Storico_Test_1[Data] ) ),
        Storico_Test_1[Data] < _date,
        Storico_Test_1[ArticoloEtichetta] = _prod_code
    )
VAR prev_date =
    MAXX ( prev_date_table, [max_date] )
VAR look_up =
    LOOKUPVALUE (
        Storico_Test_1[Acquisto Netto],
        Storico_Test_1[ArticoloEtichetta], documentidettaglio[ArticoloEtichetta],
        Storico_Test_1[Data], documentidettaglio[DataProtocollo]
    )
RETURN
    IF (
        ISBLANK ( look_up ),
        CALCULATE (
            MAX ( Storico_Test_1[Acquisto Netto] ),
            Storico_Test_1[ArticoloEtichetta] = _prod_code
                && Storico_Test_1[Data] = prev_date
        ),
        look_up
    )

Thanks in advance for all your support

[mark_endicott]

@Netdog2001ta - Hopefully I understand your requirement right, it is to return the Purchase Price into a column in table 1 when the date matches, and if it does not, it should use the purchase price from the previous date. If this is the case you can use the DAX below, to create a calculated column in Table 1.

Purchase Price = 
VAR _date = BaseTable[Date of movement]
VAR _prod_code = BaseTable[Product_code]
VAR prev_date_table =
    CALCULATETABLE (
        ADDCOLUMNS ( LookupTable, "max_date", MAX ( LookupTable[Date of movement] ) ),
        LookupTable[Date of movement] < _date,
        LookupTable[Product_code] = _prod_code
    )
VAR prev_date =
    MAXX ( prev_date_table, [max_date] )
VAR look_up =
    LOOKUPVALUE (
        LookupTable[Purchase price],
        LookupTable[Product_code], BaseTable[Product_code],
        LookupTable[Date of movement], BaseTable[Date of movement]
    )
RETURN
    IF (
        ISBLANK ( look_up ),
        CALCULATE (
            MAX ( LookupTable[Purchase price] ),
            LookupTable[Product_code] = _prod_code
                && LookupTable[Date of movement] = prev_date
        ),
        look_up
    )

In my code, BaseTable is table 1 and LookupTable is table 2. 

Screenshots to show this is working for me:

Please see that I have changed the product codes in the 2nd row of both tables, and the date of movement in the same row of the first table.

If this works for you, please mark it as the solution.