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 per...
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])
- Greg_Deckler
Community Champion
I guess I made the assumption that the tables were related on item.
