Get Exchange rates from two different periods and regions and calculate the current value

[Telstar]

Hi All

Am stuck on how to explain this let alone achieve in Power BI !! Here goes:

I have an amountU$ for a period and region.
I have exchange rates for that period and region

I also have exchange rate for 'current period' and region

What I need to do is take the amountU$ divide, it by the ex rate for that period and region, then multiply that result by the ex rate for the 'current period' and region

so in one table i have

Region; Period; AmountUS;

EUR ; Period1; 10000

ASA;Period1;100

AUS;Period1;50

Ex table

Region;Period;ExRate

EUR;Period1;1.1

EUR;Period4;1.2

ASA;Period1;1.5

ASA;Period4;1.75

AUS;Period1;0.8

AUS;Period4;0.9

(where period4 is the current period)

Want I want to end up with is the following

Region;OrigAmtU$;NewAmtU$

EUR;10000;10909       >>> 10000/1.1*1.2

ASA;100;117               >>> 100/1.5*1.75

AUS;50;56                   >>> 50/0.8*0.9

I can do the simple AmountU$ / ex rate Period 1

BUT I cannot seem to be able to create a measure for the Period4 that I can use multiply 

Any help / suggestions much appreciated.

Thanks

Telstar

[v-eachen-msft · Accepted solution]

Hi @Telstar ,

According to your sample data, you could use LOOKUPVALUE() function to find the data.

Column =
VAR a =
    LOOKUPVALUE (
        'Ex Table'[ExRate],
        'Ex Table'[Region], 'Table'[Region],
        'Ex Table'[Period], 'Table'[Period]
    )
VAR b =
    LOOKUPVALUE (
        'Ex Table'[ExRate],
        'Ex Table'[Region], 'Table'[Region],
        'Ex Table'[Period], 4
    )
RETURN
    ROUND ( 'Table'[AmountUS] / a * b, 0 )

If you have created relationships between there tables, you could also use RELATED() function.

Or you could create a new table to save Period4's data.

Here is my test result and test file for your reference,

[v-eachen-msft · Accepted solution]

Hi @Telstar ,

According to your sample data, you could use LOOKUPVALUE() function to find the data.

Column =
VAR a =
    LOOKUPVALUE (
        'Ex Table'[ExRate],
        'Ex Table'[Region], 'Table'[Region],
        'Ex Table'[Period], 'Table'[Period]
    )
VAR b =
    LOOKUPVALUE (
        'Ex Table'[ExRate],
        'Ex Table'[Region], 'Table'[Region],
        'Ex Table'[Period], 4
    )
RETURN
    ROUND ( 'Table'[AmountUS] / a * b, 0 )

If you have created relationships between there tables, you could also use RELATED() function.

Or you could create a new table to save Period4's data.

Here is my test result and test file for your reference,

[Telstar · Accepted solution]

Hi @v-eachen-msft 

Thanks - works perfect

@Telstar