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

Hi Anonymous 

try

test_roi = CALCULATE(
FIRSTNONBLANK((test_lms_loan_roi[eff_rate]), 1), 
FILTER(ALL(test_lms_loan_roi), 
SELECTEDVALUE(lms_dailybalance_cashflow[Business Date]) >= test_lms_loan_roi[eff_fromdate] && SELECTEDVALUE(lms_dailybalance_cashflow[Business Date]) <= test_lms_loan_roi[eff_todate] && test_lms_loan_roi[mg_intcomp] = 158)
)

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi az38 , above formula is giving all blank values.

 

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Anonymous 

    what is 158? in what table do you create the measure?

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      az38 I am creating calculated column in my Input table where Business Date and Loan ID is there.

      158 is code for specific type of Interest. It is part of business logic that needs to be applied. But when I removed 158 condition from your formula, it does give some value but a wrong one 😞

       

      • az38's avatar
        az38
        Community Champion
        6 years ago

        So Anonymous 

        this 158 is defined in both of tables?