Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Netdog2001ta
Frequent Visitor

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 2
Netdog2001ta
Frequent Visitor

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:

Netdog2001ta_1-1718892588544.png

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
Super User
Super User

@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:

 

mark_endicott_0-1718890162753.pngmark_endicott_1-1718890205647.png

 

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.  

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.