Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
abfdjuk
Helper I
Helper I

multiple 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.

 

Code11/1212/1313/1414/1515/1616/1717/1818/1919/20
ARS 77.9416162428.0437.1582.68
AUD1.541.61.71.822.151.71.721.761.79
BRL2.643.163.683.745.444.124.164.874.82
CAD1.611.611.561.831.911.711.721.731.71
CHF1.411.51.451.511.471.281.251.271.18
CLP767.65799.64736.17    832.82882.55
CNY 9.939.2610.5108.658.768.518.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

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

3 REPLIES 3
v-jayw-msft
Community Support
Community Support

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
tamerj1
Super User
Super User

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!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors