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])
Greg_Deckler
Community Champion
Community ChampionPerhaps 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
Memorable MemberThis was exactly what I was looking for! Thanks Greg_Deckler