Forum Discussion
Helper Imultiple currency conversion for different years
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
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
3 Replies
- tamerj1
Community Champion
Hi abfdjuk
First you need to unpivot this table to have three columns: Code, Year, Coverion Factor2nd 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] )
)
abfdjukThanks. I will try this out!
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
