Forum Discussion
How to get a price based on a date range
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!!
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])
7 Replies
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])- Greg_Deckler
Community Champion
I guess I made the assumption that the tables were related on item.
v-yuta-msftCommunity Support
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?
- Greg_Deckler
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] )- Daviejoe
Memorable Member
This was exactly what I was looking for! Thanks Greg_Deckler
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...
