Forum Discussion

CJensen's avatar
CJensen
Helper I
6 years ago
Solved

Getting currency from another table

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...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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 

v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

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's avatar
CJensen
Helper I
6 years ago

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's avatar
    v-lid-msft
    Community Support
    6 years ago

    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's avatar
      CJensen
      Helper I
      6 years ago

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