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

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi,

 

Try using below DAX by creating a new column in Table 1.

 

Interest Rate = CALCULATE(FIRSTNONBLANK('Table 2'[Interest Rate],1),
FILTER(ALL('Table 2'), (AND('Table 1'[Business Date]>'Table 2'[From Date],'Table 1'[Business Date]<'Table 2'[To Date]))))

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous this formula is giving all 0.