Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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....
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    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

     

az38's avatar
az38
Community Champion
6 years ago

So Anonymous 

this 158 is defined in both of tables?

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

az38 no, it is only defined in the 2nd input table where date ranges are specified.

 

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Anonymous 

    how do you plan to choose appropriate value? Only ba period? or by period and this key? or by both period and Loan ID?

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi az38 I wanted to lookup on all the 3 parameters - Loan ID, Date Range and 158 component. Thank you for your approach, appreciate you putting your time.