Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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:
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.
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
@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.
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
6 |