Getting currency from another table

[CJensen]

Hi 

I have a bit of a problem, that I cant solve myself.

I have a salestable with sales but no exchange rate in that table. I have the currency in anothertable, but I cant seem to retrieve the exchange rate with lookupvalue. 

I get an "multiple" values error. I think it is because the exchange rate table dates only have the specifik dates and not the exact date, like the calender table. I have no idea how to add the missing dates and get the exchange rates filled out with the last non blank. 

Can you please help ?

[v-lid-msft]

Hi @CJensen ,

It seems there is a date key column in ExchangeRatesTable. If the relations between DateTable and ExchangeRatesTable bases on the datekey column, you can try to make the relation between SalesTable and DateTable in both way, then use the lookupValue function again.

If it doesn't meet your requirement, kindly share your DAX formula to me if you don't have any Confidential Information.

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more 

[CJensen]

Hi @v-lid-msft 

Thank you so much for your reply. Ive tried changing it. And cant get it to work. 

Ive attached the .pbix file with demo data, so you can see it. 

.Pbix

[v-lid-msft · Accepted solution]

Hi @CJensen ,

In your sample, we cannot get anything by Vlookup function  as there is no matching rows in both tables. So We can create calculated column using following DAX.

ExchangeRate =
VAR cu = [Currency]
VAR t =
    FILTER (
        ALL ( ExchangeRates ),
        AND (
            [Posting Date] >= ExchangeRates[FROMDATE],
            [Posting Date] <= ExchangeRates[LASTCHANGED]
        )
    )
VAR result =
    FILTER ( t, [Currency] = cu )
RETURN
    MAXX ( result, [EXCHRATE] )

But I do not know what time column you want to use in sales table, so I use positon date in your DAX to do this example.

BTW, pbix as attached.

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more 

[CJensen]

Hi @v-lid-msft thank yo so much for your help, it was exatly what I needed !!