Pulling rate from exchage rate table

[delsbg]

My rate table is constructed as such:

Currency   Date             Rate

USD          1/1/2020      1.33

USD          1/15/2020    1.31

USD          1/21/2020    1.28

I have a sales table with a sales date from almost every day.  I need to pull the exchange rate into the sales table for every day of the year even though the exchange rate table above only contains the date the rate changed.  How do I pull the exchange rate for say 1/10/2020 which of course I need 1.33?

[v-robertq-msft]

Hi, @delsbg 

According to your description, you want to pull the exchange rate into the sales table for every day of the year, I suggest you achieve it through Power query editor, you can follow my steps:

1. Go to Power query editor, click on the ‘Sales’ table, select merge query:

2. Set the merge query like this:

3. Expand the table:

4. Get this column with null values, then click “fill down”

And you can get what you want, like this:

You can download my test pbix file here

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[delsbg]

This is a good approach thank you.  Unfortunately I should have included in my original post that I need to stay in DirectQuery.  Fill down seems to kick me out of DirectQuery when I merge the two based on dates.  I will continue to fiddle with that approach though.  

[v-robertq-msft · Accepted solution]

Hi, @delsbg 

Unfortunately, merge query is not supported in direct query mode, I got the same error as yours when I tested it. My opinion is that we should use as less calculated column/table as we can in direct query mode, which can affect the performance of the report. As a result, you can consider making the merge query in the data source (like Sqlserver database), or you can just switch into import mode for more data transformation functions and faster query speed

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataInsights]

@delsbg,

Try this calculated column in the Sales table:

Exchange Rate = 
VAR vCurrentDate = Sales[Date]
VAR vMaxDate =
    CALCULATE ( MAX ( ExchangeRate[Date] ), ExchangeRate[Date] <= vCurrentDate )
VAR vResult =
    CALCULATE ( MAX ( ExchangeRate[Rate] ), ExchangeRate[Date] = vMaxDate )
RETURN
    vResult

[delsbg]

This is what it looks like when I use that formula. I'm using DirectQuery if that matters..