topic Re: multiple currency conversion for different years in DAX Commands and Tips

multiple currency conversion for different years

abfdjuk — Tue, 04 Oct 2022 12:51:03 GMT

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.

Re: multiple currency conversion for different years

tamerj1 — Tue, 04 Oct 2022 14:11:42 GMT

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] )
)

Re: multiple currency conversion for different years

abfdjuk — Wed, 05 Oct 2022 15:23:25 GMT

Thanks. I will try this out!

Re: multiple currency conversion for different years

Anonymous — Wed, 26 Oct 2022 07:35:31 GMT

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