Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have two tables, as following:
1) Table 1:
Item
Retail Price
Valid From Date
Valid To Date
An item can have more than one Retail Price, depending on the validity within the period range.
2) Table 2:
Item
Invoice Date
I would like to get the Retail Price from Table 1 and add it to Table 2 by finding the Invoice Date within the period range from Table 1.
I'm quite new to PBI, so I'm struggling to find a solution.
Can you experts please shed some light onto this? 🙂
Thank you!!
Solved! Go to Solution.
I actually got a solution mixing both your solutions, and it seems to be working 🙂
Retail Price =
VAR CurrentItem = Table2[Item]
VAR CurrentDate = Table2[Invoice Date]
RETURN
MAXX(FILTER(RELATEDTABLE(Table1), CurrentItem = Table1[Item] && Table1[From Date] <= CurrentDate && Table1[To Date] >= CurrentDate), Table1[Retail Price])
I actually got a solution mixing both your solutions, and it seems to be working 🙂
Retail Price =
VAR CurrentItem = Table2[Item]
VAR CurrentDate = Table2[Invoice Date]
RETURN
MAXX(FILTER(RELATEDTABLE(Table1), CurrentItem = Table1[Item] && Table1[From Date] <= CurrentDate && Table1[To Date] >= CurrentDate), Table1[Retail Price])
I guess I made the assumption that the tables were related on item.
@Anonymous ,
You can create a calculate column in table2 as below:
Retail Price =
VAR Current_Date = 'Table'[Invoice Date]
VAR Current_Item = 'Table'[Item]
RETURN
CALCULATE(MAX('Table1'[Retail Price]), FILTER(Table1, Table1[Item] = Current_Item && Table1[Valid From Date] <= Current_Date && Table1[Valid To Date] >= Current_Date))
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @v-yuta-msft , thanks for your reply!
I tried your solution but got the following error:
"A single value for column 'Retail Price' in table 'Table1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
On table 2 (invoice history by date) I have many lines with the same item, because we have sales happening several times, so I'm not sure if this could be what's causing the issue?
Perhaps something like:
Price Column =
MAXX(
FILTER(
RELATEDTABLE('Table 1'),
'Table 1'[Valid From Date] <= 'Table 2'[Invoice Date] && 'Table 1'[Valid To Date] >= 'Table 2'[Invoice Date]
),
[Retail Price]
)
This was exactly what I was looking for! Thanks @Greg_Deckler
Proud to be a Super User!
Hello @Greg_Deckler , thanks for your reply!
Your solution gave me only the highest value based on the date, but not on the item. I think it might be missing a filter somewhere in between...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
101 | |
68 | |
68 | |
43 |
User | Count |
---|---|
145 | |
106 | |
105 | |
90 | |
65 |