Forum Discussion

Netdog2001ta's avatar
Netdog2001ta
Frequent Visitor
2 years ago

Problem with DAX Formula

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:

ArticoloEtichettaDescrizioneQtaDataProtocolloTipoDocumentoAcquisto Netto
Product_codeDescriptionQuantityDate of movementType of ducumentPurchase price
022512Acqua Distillata201/01/2024DDT 

 

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:

ArticoloEtichettaDescrizionePrezzo NettoData
Product_codeDescriptionPurchase priceDate of movement
022512Acqua Distillata2,0001/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.

2 Replies

  • mark_endicott's avatar
    mark_endicott
    Super User
    2 years ago

    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.  

  • Netdog2001ta's avatar
    Netdog2001ta
    Frequent Visitor
    2 years ago

    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