Multiplying column by lookup value

[Anonymous]

Hi everyone,

I have a table which has one of the columns Currency code, so each line has a code like this : EUR, USD, AUD etc. I have another table with the FX Rates that has those codes as columns. The FX Rates table looks like this:

Date                AUD   USD     EUR    PLN

01.2019             x         y         z         v

02.2109             x         q         f         s

03.2019             ............. and so on

In my main table I want to multiply for example the GP (which is in local currencies) with the fx rate like this:

GP*SUM(column)

where column is one of AUD, USD, EUR etc. But I need it to lookup for which column should it multiply with automatically by looking into the main table at the currency code. Is this possible ?

[parry2k]

@Anonymous it will be easier if you unpivot your FX Rate table and then create a unique key by cocatenating Date and Currency code, and same key add in your transaction table. It will allows you to set relationship between these two tables on this new column and from there everything will be super simple

[Anonymous]

@parry2k I have already done that. But that will give me for example the USD Value of a project that has entries in May, multiplying the value with the fx rate also from May. But sometimes the users of the report want to see the values at a different fx rate than that from the current month as the entry. That's why I would have liked to create like a drop down list slicer so the user can select whatever date he wants for the fx rate.