Forum Discussion

slatka11's avatar
slatka11
Helper I
3 years ago
Solved

Lookup value if date falls between 2 dates

I have the two below tables, one with invoice dates and the other with exchange rate date ranges, which I am trying to lookup the exchange rate based on if the invoice date falls within a date range:...
Need Help
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    Hi slatka11 

    Sorry, there are some misconderations.  Now this:

    1) create a measure with this:

     

    Rate = 
VAR _date = MAX(Table1[InvoiceDate])
RETURN
MINX(
    FILTER(
        Table2,
        Table2[StartDate]<=_date
             && Table2[EndDate]>=_date
     ),
Table2[ExchRate]
)

     

    2) plot a table with the measure and the [InvoiceDate] column. 

     

    I tried and it worked like this:

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi,

 

I'm trying to create a DAX formula that looks up a value from table 2 (xyz table), based on conditions from table 1 (abc table). I need the date from table 1 to be inbetween the start date column and end date column from table 2. Below is the formula I've tried to use:

 

Column = LOOKUPVALUE(
    abc[sale],
    abc[Network], xyz[Network],
    abc[Country], xyz[Country],
    abc[Start Date], xyz[Date]
    abc[End Date] >= xyz[Date]
)

I keep getting an error message because DAX isn't excepting ">=" expression on the last condition. Readng into to "," always means less than or equal to ("<="), what symbol can I use in this formula for great than or equal to (">=").

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    Write this calculated column formula in the abc table

    =calculate(sum(xyz[sale]),filter(xyz,xyz[Network]=earlier(abc[Network])&&xyz[Country]=earlier(abc[Country])&&xyz[Start Date]<=earlier(abc[Date])&&xyz[End Date]>=earlier(abc[Date])))

    Hope this helps.