Power BI Date Range Lookup

[Anonymous]

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 

[NikitaDalela]

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

[Anonymous]

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

[Greg_Deckler · Accepted solution]

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]

@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]

@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]

Hi, @Greg  Thank you for the referral.

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

[edhans]

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]

Appreciate the input! 

Have a great day! 🙂

[Anonymous]

Hi @Greg_Deckler thanks a lot for your approach 🙂 It is giving proper results !!

[Greg_Deckler]

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

[NikitaDalela]

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]

Hi @NikitaDalela this formula is giving all 0.

[az38]

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]

Hi @az38 , above formula is giving all blank values.

[az38]

@Anonymous 

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

[Anonymous]

@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]

So @Anonymous 

this 158 is defined in both of tables?

[Anonymous]

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

[az38]

@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]

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.