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

     

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.

  • JulieP's avatar
    JulieP
    Frequent Visitor
    4 years ago

    Hi, Greg  Thank you for the referral.

     

    For my learning, how would you do this in DAX?

    • edhans's avatar
      edhans
      Community Champion
      4 years ago

      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.

      • JulieP's avatar
        JulieP
        Frequent Visitor
        4 years ago

        Appreciate the input! 

         

        Have a great day! 🙂