Multiple Currency - Reporting

[Matty]

Dear DNA Team,

Conversion Rate.pbix (73.9 KB) Sales.xlsx (68.6 KB) GBP.xlsx (8.7 KB) USD.xlsx (8.7 KB)

I am seeking your advice on how to handle multiple currencies.

The attached data contains payment dates in different currencies. The goal is to convert each sale to GBP or USD base on the selection.

There are two exchange rates conversion files one to GBP other to USD. Both are appended in the Power BI.

The below report shows the sales in different currencies:

The exchange currency rate is provided monthly not on a daily rate, in order to calculate exchange rate o daily level I used two different patterns that gave me exactly the same result:

1. Monthly Exchange Rate =
   CALCULATE(
   AVERAGEX(
   ‘Calendar’
   ,[Ex Rate]
   )
   ,DATESMTD(‘Calendar’[Date])
   )
2. Exchange Rate =

VAR MonthYear = SELECTEDVALUE(‘Calendar’[Month&Year])

Return

CALCULATE([Ex Rate],
FILTER(ALL(‘Calendar’),
‘Calendar’[Month&Year] =MonthYear))

What I am trying to get is to convert each sale to USD and GBP:

If I select currency slicer then the given exchange rate is shown correctly:

Relationship:

Since the sales are in different currencies and I would like to display them in GBP and USD what would be the best approach?

I was trying to use the lookup value formula but then this didn’t work

I tried to use the pattern that Nick used in one of the miniseries:

Converted Sales =
VAR _Date = [Current Date] --Current Date in the Current Filter Context
VAR _CurrencySelected = [Currency Selected] --Currency Selected in the Current Filter Context
VAR _Sales = [Total Sales] --Measure we want to convert
VAR _Rate =
LOOKUPVALUE(
FactCurrencyRates[Crossrate] --Value to Bring back
,FactCurrencyRates[Ticker],_CurrencySelected --using the currency selected, lookup that value in the fact table
,FactCurrencyRates[Date],_Date --using the current date, lookup that value in the fact tabel
) /End Result is the currency rate of the currency selected on the current date/

VAR _ConvertedMeasure = _Sales * _Rate

RETURN
_ConvertedMeasure

Would you suggest creating a Variable Table (using addcolumns summarize)?

I will appreciate your advice as I would like to give it a go.

Thank you,

Matty

[Melissa]

Hi @Matty,

@Nick_M did 3 videos on handling currencies, you can find them and more here.

Enterprise DNA

I hope this is helpful.

[Matty]

@Melissa Thank you,

I spend some time watching these videos, I even studied the @Nick_M file

Handling Missing Currency Dates via PQ.pbix (6.4 MB)

Nick and Sam used the data that had sales in one currency and they were converting to multiple currencies, so they could use lookup formula whereas I don’t know how to apply it to my source since the sales are in multiples currencies,

I am trying to tackle this topic using DAX, but I can’t get it right.

Conversion Rate.pbix (74.0 KB)

Converted Sales =

VAR _ExchangeRate =

ADDCOLUMNS(
SUMMARIZE(‘Currency’,‘Currency’[Currency],‘Currency’[FXto],
‘Calendar’[Date]),“FX Amount”,[Exchange Rate])

VAR _CurrencySelected = [CurrencySelected]

Return

SUMX(
_ExchangeRate,
[Total Sales] / [Exchange Rate])

;/

[Greg]

Hi @Matty. I recall that that was one of the issues tackled in Enterprise DNA Challenge 10. My solution was to first convert all sales currencies into a single base currency (doesn’t matter which one … I chose CDN as it was easy to download exchange rate data from the Bank of Canada, and, well, I’m Canadian, so … ), then to the desired reporting currency. Hope it helps.
Greg

[Matty]

Thank you @Greg for your response,

Would you convert all sales into one currency in Query Editor? I will have a look at DNA Challenge 10.

I really appreciate your help, I just can’t get it right in DAX whereas in the m-code I did, but I would prefer to use DAX since it wouldn’t slow down the file so much as the m code for large data sets.

As per attached v2.

Conversion Rate v2.pbix (76.5 KB)

In the query editor I added 2 columns that give us an exchange rate to USD and to GBP:

In order to calculate FX amount firstly, I had to add a date column, since I am using monthly exchange rates then I could add a simple start of the month date and then use the below m-code to pull FXamount from the Currency Table:

Currency{[Currency = [Base Curr],Date = [Start of Month], FXto =“GBP” ]}[FX Amount]

Then, I was able to calculate USD and GBP amounts:

GBP Sales =

SUMX(Sales,
Sales[Gross Invoice Amount] / Sales[GBP])

To ensure that the pattern works correctly, I did check in the excel for 12/02/2021

Trial Conversion.xlsx (9.5 KB)

honestly, I appreciate your help to tackle this topic in DAX.

Thank you,

Matty

[Matty]

Hi @Greg

I’ve checked the data for Challenge 10 all sales were in one currency actaully value.

Thank you,

Matty

[Greg]

Hi @Matty. Oops, wrong challenge … please see my C9 submission (sales were in either GBP, USD, or EUR). Here’s the link to my writeup, where I mostly used DAX.
Greg

Power BI Challenge 9 - Currency Conversion and More from Greg

(I fixed the DAX formatting from my original post [removed “Hide Details” and added non-indented and blank lines above and below each DAX code block.) Attributes: Icons made by Freepik from www.flaticon.com What I liked: I’m glad to have taken the opportunity to explore currency conversion; although I didn’t spend much time verifying the results, the basic mechanics of currency conversion are now in my arsenal, and I can explore further when the need arises I like that my solution is no…

[deltaselect]

Hi Matty,

I am not sure if you have your issue sorted out, but I want to present a way of how I approached this multi currency reporting issue,

1. reshuffle the currency exchange rate table with an unique Currency Key (Year & Month & Base Currency) (need to add year month column with some M code or in Excel ), see example below.

2. Add the currency key also to the Payments Fact table.

3. Make a one to many relationship between the 2 tables.

4. Make 3 simple measures ;

• Total Payments base curr = SUM( DSPayments[Gross Invoice Amount] )
• Total Payments in GBP = SUMX( DSExchRate, [Total Payments base curr] / DSExchRate[GBP FX Amount] )
• Total Payments in USD = SUMX( DSExchRate, [Total Payments base curr] / DSExchRate[USD FX Amount] )

That gives already numbers, in Local Currency, GBP and USD.

see PBIX [Payment Reporting in different currencies.
Payment Reporting in different currencies.pbix (81.9 KB)

Probably an virtual table approach within the measures is more advanced or better then adding the columns.

Kind regards,

[Matty]

Thank @deltaselect

I will have a look,

Thank you,

Matty

[Matty]

@deltaselect I manage to rebuild as per your example thank you very much for your insight. I must say, first when I read :

• Total Payments in GBP = SUMX( DSExchRate, [Total Payments base curr] / DSExchRate[GBP FX Amount] )

I wonder how this works, but then I remind myself that there is a relationship between exchange rate (unique table) and sales table

@Greg Would it be possible for you to share the file? I would like to understand what have you done.

Conversion Rate v2.pbix (86.9 KB)

I do agree “an virtual table approach within the measures is more advanced or better then adding the columns” .

I am still tackling this subject with conversation rate as I wonder do we need to add extra columns in query editor meaning the unique key.

Now I am trying to rebuild the below m-code pattern but using Dax ( I belive if this was possible to do in m-code should be available to do in DAX)

Currency{[Currency = [Base Curr],Date = [Start of Month], FXto =“GBP” ]}[FX Amount]

Test 2 =

VAR _StartMonth = STARTOFMONTH(Sales[Payment Date])

VAR _Currency = SELECTEDVALUE(Sales[Base Curr])

Return

SUMX(Sales,

Sales[Gross Invoice Amount] /

LOOKUPVALUE(‘Currency’[FX Amount],

‘Currency’[Date],_StartMonth,

‘Currency’[Currency],_Currency))

The above pattern doesn’t work yet as I assuming I have done something wrong.

Conversion Rate v2.pbix (86.9 KB)

[Greg]

Hi @Matty. Challenge PBIX files (including mine) are available to forum members in the challenge showcase course.
Greg

[Matty]

@Greg

apologies, where do you exactly mean?

I am user of DNA Enterprise and on the forum I can see the below:

As a member:

For the below I can only see raw data:

Thank you,

Matty

[Greg]

Hi @Matty.
(1)

(2)

(3)

(4)

(5)

[Matty]

Got yaa @Greg,

Thank you very much for sharing it, I will have a look tomorrow morning

[Matty]

Dear @Greg,

I had a look into your model and there is one difference that stops me from the correct result.

We both have a transaction table with multiple currencies but the exchange rate table(currency table) you have only conversion into CAD dollars whereas my FX has to USD and GBP.

Example:

This afternoon I was struggling to tackle multiple currencies, as I have multiple currencies in Dim table and multiple currencies in the fact table but I would like to display values from the fact table in USD and GBP.

I manage to get the correct result using m-code by adding extra columns and then build simple DAX formula.

2nd solution was provided by @deltaselect (we had add additional columns and then just build simple dax pattern)

Then I thought I don’t want to add additional columns or transform anything, so I tried to rebuild the m-code pattern but in DAX:

Currency{[Currency = [Base Curr],Date = [Start of Month], FXto =“GBP” ]}[FX Amount]

Firsty I used:
Test 2 =

VAR _StartMonth = STARTOFMONTH(Sales[Payment Date])

VAR _Currency = SELECTEDVALUE(Sales[Base Curr])

Return

SUMX(Sales,

Sales[Gross Invoice Amount] /

LOOKUPVALUE(‘Currency’[FX Amount],

‘Currency’[Date],_StartMonth,

‘Currency’[Currency],_Currency))

But the formula didn’t work,

then I used:

Test 3 =

VAR _StartMonth = STARTOFMONTH(Sales[Payment Date])

VAR _Currency = SELECTEDVALUE(Sales[Base Curr])

Return

SUMX(Sales,

Sales[Gross Invoice Amount] /

CALCULATE(

LOOKUPVALUE(‘Currency’[FX Amount],

‘Currency’[Date],_StartMonth,

‘Currency’[Currency],_Currency),

‘Currency’[FXto] = “USD”))

Once again didn’t work

I tried one more time:

Test 4 =

VAR _StartMonth = STARTOFMONTH(Sales[Payment Date])

VAR _Currency = SELECTEDVALUE(Sales[Base Curr])

VAR _USD = “USD”

Return

SUMX(Sales,

Sales[Gross Invoice Amount] /

LOOKUPVALUE(‘Currency’[FX Amount],

‘Currency’[Date],_StartMonth,

‘Currency’[Currency],_Currency,

‘Currency’[FXto],_USD))

the formula works but not fully as it gives infinity results or NAN.

Could please help me with how to handle this error?

Conversion Rate v2.pbix (88.6 KB)

Thank you,

Matty

[Greg]

Hi @Matty. I expect you’re getting the “infinity” error as there’s no matching record returned and arithmetic division is throwing an error. Try using the DIVIDE command instead. As for the “NaN” error, I expect you’re dividing zero by zero. To debug further, I’d create a temporary full table with all the interim values you will be using in your TEST4 measure, where you can see them individually; hopefully the surce of the error will stick out like a sore thumb. Hope it helps.
Greg

[deltaselect]

Hi Matty,

This formula below works . see also screenshot below.
You need to have 2 measures, one for GBP and one for USD.
This formula below is setup for the GBP and USD exchange rates in 2 columns, and you need to have a one to many relationship for exchange rates to the date table.
If you want to have only one column (USD and GBP under each other) in the exchange rate table, then presumably you need an additional filter per measure to avoid the Lookup to find 2 values instead of just one. I check this out.

Totals derived from the DAX formulas equals with the totals from my method with the additional columns /keys, so the DAX formula works fine.

Payments in GBP DAX = SUMX( Payments, 
        Payments[Gross Invoice Amount] /
            LOOKUPVALUE( ExchRate[GBP FX Amount],
                  ExchRate[Date] , STARTOFMONTH( Payments[Payment Date]),
                  ExchRate[Base Currency], Payments[Base Curr]))

Source is a video from Sam MacKay.

Handling Multiple Currencies in Power BI w/DAX

[Matty]

@Greg Found the issue,

I don’t understand why DAX struggles to convert multiple sales (meaning more than one currency) lookup pattern should work… correctly.

Test 6 =

VAR _StartMonth = STARTOFMONTH(Sales[Payment Date])

VAR _Currency = SELECTEDVALUE(Sales[Base Curr])

VAR _USD = “USD”

Return

SUMX(Sales,

DIVIDE(Sales[Gross Invoice Amount] ,

LOOKUPVALUE(‘Currency’[FX Amount],

‘Currency’[Date],_StartMonth,

‘Currency’[Currency],_Currency,

‘Currency’[FXto],_USD)))

@deltaselect Yes, correct:

I don’t need the relationship between Exchange rate table and the sales table

The formula pattern provide correct result:

Payments in USD DAX = SUMX( Sales,

[Total Sales] /

LOOKUPVALUE( ExchangeRate[USD Amount],

ExchangeRate[Attribute] , STARTOFMONTH( Sales[Payment Date]),

ExchangeRate[Currency], Sales[Base Curr]))

I do get the final result correct but I would like to fix my pattern using the below currency table:

where all the amounts are in one column:

Therefore I tried to use:

Test 6 =

VAR _StartMonth = STARTOFMONTH(Sales[Payment Date])

VAR _Currency = SELECTEDVALUE(Sales[Base Curr])

VAR _USD = “USD”

Return

SUMX(Sales,

DIVIDE(Sales[Gross Invoice Amount] ,

LOOKUPVALUE(‘Currency’[FX Amount],

‘Currency’[Date],_StartMonth,

‘Currency’[Currency],_Currency,

‘Currency’[FXto],_USD)))

and then create the same measure for GBP, once it works I will both measures with Switch logic. I will appreciate @Greg if you could help me to fix this pattern.

This is on my head I can’t go to bed without solving it as I am thinking about it all the time

I will appreciate it if you can help me to tackle this as Test 6 formula vs payments in USD is nearly the same.

Conversion Rate v2.pbix (89.0 KB)

[deltaselect]

Hi Matty,

maybe this helps you further:

1. Use the original Exchange rate table with USD & GBP in one column under each other.
   Press New Table, use CALCULATETABLE to create a new table, see below for GBP.
   P.S.: It seems not possible to use a CALCULATETABLE as a Variable in the measure

GBPExchRateTable = CALCULATETABLE('ExchRates 1 Column', 'ExchRates 1 Column'[FXto] ="GBP")

2. Adjust the lookup formula in the measure for payments, converted into GBP, to the shortened GBP Exchange rate table, see below

   Payments in GBP DAX Rates from 1 Column = 
      SUMX( Payments, 
           Payments[Gross Invoice Amount] /
               LOOKUPVALUE(GBPExchRateTable[FX Amount],
                     GBPExchRateTable[Date] , STARTOFMONTH( Payments[Payment Date]),
                     GBPExchRateTable[Currency], Payments[Base Curr]))
   

3. Repeat steps 1 and 2 for USD

[Matty]

Hi @deltaselect

I can’t use the Calculatable for the currency table.

There must be a way to create a variable table only for USD values. I just don’t want to create extra columns, etc. I believe if this was possible to do in query editor using m-code then DAX also should handle it.