IF or VLOOKUO formula with multiple contidions in another table

[Anonymous]

Hi, I am struggling to get the right exchange rate from an exchange rate table to a Project dimension table  to translate the Agreed price into USD.

The exchange rate table looks like this:

fExchange_rates
Start_date	End_date	Currency	Daily_rate
04-dec-19	19-dec-19	EUR	2,50
20-dec-19	14-jan-20	EUR	2,60
15-jan-20	17-feb-20	EUR	2,70
18-feb-20	24-apr-20	EUR	2,80
25-apr-20	30-apr-20	EUR	2,90
01-mei-20	11-mei-20	EUR	3,00
12-mei-20	27-mei-20	EUR	3,10
03-feb-19	28-feb-19	GBP	5,40
01-mrt-19	03-apr-19	GBP	5,50
04-apr-19	09-jun-19	GBP	5,60
10-jun-19	15-jun-19	GBP	5,70
16-jun-19	26-jun-19	GBP	5,80

The second table, where the exchange rate needs to be applied, looks like this:

dProjects
Agreed price (curr)	Currency	Creation_date	Daily rate to apply
100.000	EUR	10-apr-20
100.000	GBP	10-apr-19

The two tabel are connected via a currency dimension table (only connection).

The correct exchange rate meets all of the following criteria:

1. dProjects[Currency]             =     fExchange_rates[Currency]
2. dProjects[Creation_date]  >=  fExchange_rates[Start_date]
3. dProjects[Creation_date]  <     fExchange_rates[End_date]

The result should be 2,80 for the first row, and 5,60 for the second.

I have tried following formula:

Daily rate to apply = IF(

dProjects[Creation_date] >= fExchange_rates[Start_date]

&& dProjects[Creation_date] < fExchange_rates[End_date]

&& dProjects[Currency] = fExchange_rates[Currency] 

;

fExchange_rates[Daily_rate]; "")

However, this delivers the following error message:

A single value for column 'Daily_rate' in table 'fExchange_rates' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Next try was this formula:

Daily rate to apply =

CALCULATE(

SUM(fExchange_rates[Daily_rate]);

dProjects[Creation_date] >= fExchange_rates[Start_date]

&& dProjects[Creation_date] < fExchange_rates[End_date]

&& dProjects[Currency] = fExchange_rates[Currency] 

)

Which brings this error message:

The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.

Anybody who can help me?

[Anonymous]

If you are not opposed to using Power Query, you can use the following code and it will the fill the holes in the data so there's always a date and rate:

In Power Query, Add a custom Column and enter in:

List.Dates(
[Start_date],
Duration.Days( [End_date] - [Start_date] ) +1,
#duration(1,0,0,0)
)

This will produce a list of all the dates between, and including, the start and end dates.

Expand that column to New rows

Remove the Start and End Date columns, and now you have a table with all the dates and can write a much easier measure.

This can be done in DAX, but I'd rather write easier DAX and have power query do some of the work.

[Anonymous · Accepted solution]

Hi Nick, thanks for your reply and proposed solution. In the meantime a colleague came up with the following solution which also works for me:

FX rate to US$ =

CALCULATE (

FIRSTNONBLANK ( fExchange_Rates[Current_rate]; 1 );

FILTER (

fExchange_Rates;

dProjects[Creation_date] >= fExchange_Rates[Start_date]

&& OR( dProjects[Creation_date] <= fExchange_Rates[End_date] ; ISBLANK(fExchange_Rates[End_date]) )

&& fExchange_Rates[Currency] = dProjects[Currency]

)

)

Regards, Bart