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.

 

 

I did the research and came across below issue. 

https://community.powerbi.com/t5/Desktop/DATE-RANGE-LOOKUP/td-p/420514

But, the formula given in this post throws me an error "A table of multiple values was supplied where a single value was expected"

 

Formula : 

test_roi = CALCULATE(VALUES(test_lms_loan_roi[eff_rate]), FILTER(test_lms_loan_roi, lms_dailybalance_cashflow[Business Date] >= test_lms_loan_roi[eff_fromdate] && lms_dailybalance_cashflow[Business Date] <= test_lms_loan_roi[eff_todate] && test_lms_loan_roi[mg_intcomp] = 158))
 
I also tried using AVERAGE,MIN,MAX,FIRSTNONBLANK function instead of VALUES but none gave me expected output.
 
Can someone please help me. GilbertQ 

 

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

     

20 Replies

  • 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

    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.

       

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    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 Greg_Deckler thanks a lot for your approach 🙂 It is giving proper results !!

       

       

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        6 years ago

        Great! I didn't test that code so that's good to know!! 🙂

         

    • JulieP's avatar
      JulieP
      Frequent Visitor
      4 years ago

      Greg_Deckler 

       

      I am super new to PowerBI and I think I have a similar query as stated in this thread. I am trying to map the solution to fit my requirements but it is just resulting to a blank cell in power query.

       

      I have a specific date and I want to look this up to return a value against two dates.

       

      Table 1: I am trying to find the value of Term Code from Table 2 that is between the Term End Date and the End Date Threshold:

       

      Table 2: 

       

      I was able to achieve this in excel by using the formula: XLOOKUP(C1,$K$2:K5,$M$2:M5,"",-1,1) and i want to replicate this is powerquery:

       

      Resulting Table:

       

      This Question may be elementary but I have been trying to work this out for a while now through google search and youtube videos to no avail.

       

      Thanks.

       

       

       

       

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        4 years ago

        JulieP I would recommend starting a new thread in the Power Query forum and tagging people like ImkeF edhans I don't know the Power Query way to do that, just the DAX way.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Anonymous : I tried it on PBI Desktop and it is giving the required result.

     

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you Anonymous for investing time in my issue, Probably I have some data issue due to which your formula is not working for me.