Forum Discussion
Helper ILookup 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:
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.
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:
6 Replies
FreemanZSuper User
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:
- 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.
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.
