multiple currency conversion for different years

[abfdjuk]

Hi.

I have a table which has budgeted currency exchange rates for 20 different currencies for the last 10 years.

Code	11/12	12/13	13/14	14/15	15/16	16/17	17/18	18/19	19/20
ARS		7	7.94	16	16	24	28.04	37.15	82.68
AUD	1.54	1.6	1.7	1.82	2.15	1.7	1.72	1.76	1.79
BRL	2.64	3.16	3.68	3.74	5.44	4.12	4.16	4.87	4.82
CAD	1.61	1.61	1.56	1.83	1.91	1.71	1.72	1.73	1.71
CHF	1.41	1.5	1.45	1.51	1.47	1.28	1.25	1.27	1.18
CLP	767.65	799.64	736.17					832.82	882.55
CNY		9.93	9.26	10.5	10	8.65	8.76	8.51	8.76

How do I apply this to my invoices received table which has column headers of:

- currency code

- invoice net

- invoice year

Many thanks.

DJ

[v-jayw-msft · Accepted solution]

Hi @abfdjuk ,

Select [Code] column then unpivot other columns in Power Query Editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZDLCoNADEV/pbiW4GQeySytpXQhpVi6KOL//0YnuQp2kTkkk5vXunbj8u767tJMzKimhlCOh81lpcEYhUJuVKai3dY39edmmZRdRS4j8VfZlBAcMWFgz6te47rMnlm8BXnbaA0MYsFMyZAoMFAcKgB7mWnEKCWckdFKo6MiKOFvmngEvczj7m7a9XCAjFjCMqwA/hjBgLtM88uuWYSKfUutWE9ioSD7wc+mkXExbcfNGUWe3/23Uo0O9nUGjDVYOhoobqoY0b1t+wE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, #"11/12" = _t, #"12/13" = _t, #"13/14" = _t, #"14/15" = _t, #"15/16" = _t, #"16/17" = _t, #"17/18" = _t, #"18/19" = _t, #"19/20" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}, {"11/12", type number}, {"12/13", type number}, {"13/14", type number}, {"14/15", type number}, {"15/16", type number}, {"16/17", type number}, {"17/18", type number}, {"18/19", type number}, {"19/20", type number}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Code"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Value", "exchange rates"}, {"Attribute", "year"}})
in
    #"Renamed Columns"

Then create measure like:

measure = 
var ex_rate = calculate(max('table'[exchange rates]),filter('table','table'[code] = selectedvalue(invoice[code])&&'table'[year]=selectedvalue(invoice[year])))
return
ex_rate*sum(invoice[amount])

Best Regards,

Jay

[tamerj1]

Hi @abfdjuk 
First you need to unpivot this table to have three columns: Code, Year, Coverion Factor

2nd you need to have a dimention date table that filters both the currency conversion table and the sales table.

Then sales amount for example would be something like
SUMX ( 
VALUES ( 'Date'[Year] ),
CALCULATE ( SUM ( Sales[Sales] ) ) * SELECTEDVALUE ( CurrencyConversion[Conversion Factor] )
)

[abfdjuk]

Thanks. I will try this out!