Latest currency conversion from other table

[Will2020]

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

[v-yiruan-msft]

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]

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

[v-yiruan-msft]

Hi @Will2020 ,

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

Best Regards

Rena

[Will2020]

hi

thanks again for your help

I can't create a relationship because there is technically a many to many relationship, as for each currency there are 5 or so lines?

I can't create a relationship between a column on the revenues table, and a measure on the conversion ( can't link between a measure?)

Maybe I could create a new table which just pulls only the latest data from the conversion table...

[v-yiruan-msft]

Hi @Will2020 ,

Could you please provide some sample data in table vwRevenues? Later we will check if there are other feasible ways to achieve it.

Best Regards

Rena

[Will2020]

Here 

the issue is that there are 1000's of REVENUE_ID's and each one has a different CURRENCY.

So you could tie that CURRENCY column to the conversion rates table, but the conversion rates also has multiple FROM_CURRENCY (as it is updated yearly). So it creates a many>many relationship.

I need to tie the CURRENCY to the measure which you helped me create, but that seems to be impossible with directquery. 

I have tied a custom column through a relationship before, though. So maybe we could generate a column with the measure value in it?

thanks again for your help.

[v-yiruan-msft · Accepted solution]

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