Lookup value if date falls between 2 dates

[slatka11]

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:

TABLE 1

Invoice Date

11/17/2022

12/16/2022

12/17/2022

12/18/2022

1/3/2023

TABLE 2

EXCH Start Date	EXCH End Date	EXCH Rate
10/1/2022	10/31/2022	1.28
11/1/2022	11/30/2022	1.29
12/1/2022	12/31/2022	1.3
1/1/2023	1/31/2023	1.31
2/1/2023	2/28/2023	1.32

How would I write a column or measure to lookup the exchange rate based on the invoice date? Thank you.

[Salva]

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]

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.

[Ashish_Mathur]

Hi,

Create this calculated column formula in Table 1.

Exchange rate = calculate(sum('Table 2'[EXCH rate]),filter('Table 2','Table 2'[Exch Start Date]<=earlier('Table 1'[Invoice date])&&'Table 2'[Exch End Date]<=earlier('Table 1'[Invoice date])))

Hope this helps.

[FreemanZ]

hi @slatka11 

Try to add a column in Table1 with this:

Rate =

VAR _date = [InvoiceDate]

RETURN

MINX(

    FILTER(

        Table2,

        Table2[StartDate]<=_date

             && Table2[EndDate]>=_date)

     ),

Table2[ExchRate]

)

[slatka11]

hi @FreemanZ, 

I get to the last part of the formula where you have Table2[ExchRate] but when I try to save the formula I get an error "The syntax for ',' is incorrect

Am I doing something wrong? When I start typing the last part (Table2[ExchRate]) it appears as grey text

[FreemanZ · Accepted solution]

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: