Forum Discussion

Will2020's avatar
Will2020
Frequent Visitor
6 years ago
Solved

Latest currency conversion from other table

I have a table as above called vwConversion which shows conversion rates between USD and other global currencies. There is one conversion rate per year produced on 01/10. 

There are multiple orders stored in any currency (all of which appear in the above table column "FROM_CURRENCY").

I want to display the value for each order in USD. The newest conversion rate is what I wish to use to convert.

I am not sure the best method to do this conversion using directquery.

I am tying the two tables on the FROM_CURRENCY and in the vwRevenues table there is a column called CURRENCY.

thanks for any help

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Will2020 ,

    Please try to create a calcualted table as below, then create the relationship between new created table and vwRevenues based on currency field.

    Table = 
SUMMARIZE (
    VALUES ( vwConversion[FROM_CURRENCY] ),
    'vwConversion'[FROM_CURRENCY],
    "LatestConversionRate", CALCULATE (
        MAX ( 'vwConversion'[CONVERSION_RATE] ),
        FILTER (
            ALLSELECTED ( 'vwConversion'[CONVERSION_DATE] ),
            'vwConversion'[CONVERSION_DATE] = MAX ( 'vwConversion'[CONVERSION_DATE] )
        )
    )
)

    Best Regards

    Rena

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Will2020 ,

    You can create a measure as below to get the newest conversion rate:

    Measure =
CALCULATE (
    MAX ( 'Table'[CONVERSION_RATE] ),
    FILTER (
        ALLSELECTED ( 'Table'[CONVERSION_DATE] ),
        'Table'[CONVERSION_DATE] = MAX ( 'Table'[CONVERSION_DATE] )
    )
)

    Best Regards

    Rena

    • Will2020's avatar
      Will2020
      Frequent Visitor
      6 years ago

      thanks very much, that actually works to display the table as you shown.

      However, I am struggling to see how to actually apply that to a matrix view visual. I tried to create a new measure, which would multiply the amount in native currency by the USD conversion rate (labelled 'measure' in the currency conversion table) but I can't get it to link the two tables. I tried using LOOKUPVALUE but it doesn't seem to work either in DirectQuery or it just doesn't work spanning two tables which seems pointless.

      Basically now I can make a table which shows the latest conversion rate but simply multiplying the amount of each order by that seems to be impossible...

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Will2020 ,

        Did you create any relationship between table vwConversion and vwRevenues just like below?

        Best Regards

        Rena