Forum Discussion
Power BI Date Range Lookup
I have below two tables. I need to lookup on my date range table and fetch the Interest rate if the business date lies between the from date and to date. The expected output looks like below....
Generally I do something like:
Interest Rate Column = VAR __Date = 'Table1'[Business Date] VAR __ID = 'Table1'[Loan ID] VAR __Rate = MAXX( FILTER( ALL('Table2'), 'Table2'[Loan ID] = __ID && 'Table2'[From Date] <= __Date && 'Table2'[To Date] >= __Date ), [Interest Rate] ) RETURN __Rate
I answered your question in the PQ forum. DAX would be relatively simple. This is from memory:
A Lookup Measure =
VAR varCurrentDate = ThisTable[EndDate]
VAR Result =
MAXX (
FILTER (
TheLookUpTable,
TheLookupTable[StartDate] <= varCurrentDate
&& TheLookupTable[EndDate] >= varCurrentDate
),
TheLookupTable[TermCode]
)
RETURN
Result
NOt super efficient. You should never filter an entire table, but the lookup table is probably pretty small though. Greg_Deckler could likely do it more efficiently in DAX though than I can.
Appreciate the input!
Have a great day! 🙂