cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.

 Contract table Contract No Currency Code CON001 USD CON002 EUR CON003 SEK CON004 GBP

 Contract invoicing lines Contract No Item No Line Amount CON001 100 10 000 CON001 101 2 000 CON002 100 5 000 CON003 200 3 000 CON003 240 1 000 CON004 300 20 000

 Currency exchange rates 2019-05-11 USD 10 2019-05-11 EUR 10 2019-05-11 SEK 1 2019-05-11 GBP 11 2019-05-12 USD 10,1 2019-05-12 EUR 9,9 2019-05-12 SEK 1 2019-05-12 GBP 11,2

Thanks!

Lisa B

1 ACCEPTED SOLUTION
Super User

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

9 REPLIES 9
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.

Helper III

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 No Item No Line Amount Currency code (not in table) Line Amount (LCY) (not in table) CON001 100 10 000 USD 101 000 CON001 101 2 000 USD 20 200 CON002 100 5 000 EUR 49 500 CON003 200 3 000 SEK 3 000 CON003 240 1 000 SEK 1 000 CON004 300 20 000 GBP 224 000

Hope this clarifies.

Thanks.

Super User

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

Helper III

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?

Many thanks

Lisa

Super User

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

Helper III

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

Helper III

Hi again @parry2k,

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

Helper III

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

Helper III

Hi @parry2k ,

Many thanks - works like a charm.

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

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

Microsoft Fabric & AI Learning Hackathon

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

Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors