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 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 ?

 

 

 

 

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 

4 Replies

  • 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