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, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
LisaB
Helper III
Helper III

Calculate amounts in local currency

Hi,

 

I have different amounts in currency but wish to calculate them to local currency.

 

I have three different tables, see examples below. The first table contains the currency code but no amounts. The second table (invoicing lines) does not contain currency codes but line amounts. The third table is the exchange rate table that contains one exchange rate per day. 

 

In the second table, the invoicing lines, I only want to retreive some amounts, I have marked them in bold.

 

I wish to retreive the amounts in local currency for only some invoicing lines (this will be a page level filter) using the latest exchange rate available.

 

Any advice?

 

Contract table
Contract NoCurrency Code
CON001USD
CON002EUR
CON003SEK
CON004GBP

 

Contract invoicing lines
Contract NoItem NoLine Amount
CON00110010 000
CON0011012 000
CON0021005 000
CON0032003 000
CON0032401 000
CON00430020 000

 

Currency exchange rates
2019-05-11USD10
2019-05-11EUR10
2019-05-11SEK1
2019-05-11GBP11
2019-05-12USD10,1
2019-05-12EUR9,9
2019-05-12SEK1
2019-05-12GBP11,2

 

Thanks!

 

Lisa B

1 ACCEPTED SOLUTION

@LisaB solution attached.

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

9 REPLIES 9
parry2k
Super User
Super User

@LisaB your post in not very clear:

 

what is local currency?

You mentioned for few line items you want to convert? What is the logic of few lines?

 

Can you put example of what you expected result to be?

 

Also I believe contact number in contract table (1st table) is unique?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hi @parry2k,

 

The local currency is SEK (exchange rate = 1)

The first table is the contract header where contract no is the unique key. The second table is the invoicing lines for the contracts. The purpose is to calculate the ARR and only a few items numbers are recurring revenue. However, as long as I can get the amount i SEK for each line I can put a page level filter on item number.

 

Expected result:

 

Contract NoItem NoLine AmountCurrency code (not in table)Line Amount (LCY) (not in table)
CON00110010 000USD101 000
CON0011012 000USD20 200
CON0021005 000EUR49 500
CON0032003 000SEK3 000
CON0032401 000SEK1 000
CON00430020 000GBP224 000

 

Hope this clarifies.

 

Thanks.

@LisaB solution attached.

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hi @parry2k ,

 

The solution you sent worked perfectly fine until now (?). This is how the formula now looks like. I haven't made any changes to the data or the formula.

 

Can you see why I get this error?

 

exchange.PNG

 

Many thanks

 

Lisa

@LisaB any table name or column name changed?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hi,

 

Nope, no changes. What's wierd is that I can refresh the report without any problems, it is when I look into the formula I get the error and after that it is no longer possible to refresh the report. Hope that makes sense...

 

L

Hi again @parry2k,

 

It seems like the formula doesn't pick up ResultIfFalse in the IF-statement, see picture:

 

exchange new.PNG

Hm - I hit tab ("1 ;") instead of  ";" directly after the number 1 and it now works again...

Hi @parry2k ,

 

Many thanks - works like a charm.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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