March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
Solved! Go to 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
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
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] )
)
Thanks. I will try this out!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
26 | |
16 | |
15 | |
12 | |
11 |
User | Count |
---|---|
32 | |
26 | |
24 | |
20 | |
14 |