cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JRHans09 Regular Visitor
Regular Visitor

Convert multiple currencies to USD using multiple currency Fx Table with missing weekend/other dates

I am new to PBIX, so please let me know if any additional info is needed.

 

How do I link to the correct exchange rate within an exchange table that has multiple currencies, but also is missing weekend rates and other missing dates?

 

I have created a formula for filling the weekend dates in the Forex Table, but the correct rate is not always appearing correctly.

 

I have the following table arrangement:

Tables.jpg

 

Invoice Details[ciINV] is linked to Invoices[ciINV]

Invoices[Inv Date] is linked to Dates[Date]

Dates[Date] is linked to Forex Rates[Forex Date] 

 

KMs - Revenue is a Key Measures Table

 

Some further details:

1. Tables are linked to our system directly

2. Forex Table:

a. These rates are manually input into our system because the rates for each ccurrency need to be taken from specific local governement mandated websites. As a result, some of the dates may be missing, in addition to the normal missing weekend rates.

b. Current Base currencies - VND and MYR

c. Converted To currency - USD

3. Invoices are from multiple branches with different base currencies

4. We want to report in USD for regional management

 

Below is the formula used to extend the missing Forex Dates over the weekend.

 

 

Adj Debt Fx Rate = 
//this formula uses Friday rates for weekend transactions
CALCULATE (
    MAX ( 'Forex Rates'[Debtor Rate] ),
    LASTNONBLANK (
        DATESBETWEEN ( Dates[Date], BLANK (), LASTDATE ( Dates[Date] ) ),
        CALCULATE ( COUNT ( 'Forex Rates'[Debtor Rate] ) )
    )
)

 

 

The formula is working except that are issues where the users have not input a rate during the week which seems to create a gap and then the formula finds the next non blank value which may be the incorrect currency. I think the main issue is the above formula does not cater for the necessary Base Currency per Invoice.

 

Below is the formula for Totalling in USD (Measure inside Key Measures Table):

 

Total Revenue USD = 
SUMX( Invoices, 
    IF  ( Invoices[Base Currency] = "USD", Invoices[Total Revenue Base], 
        ( [Total Revenue Base] * [Adj Debt Fx Rate] ) ) 
)

Here is a suppliemental formula I am using to check what the Forex Rate is per Invoice: (appears in the tables below)

Forex Check = [Total Revenue USD] / [Total Revenue Base]

Here are some screen shots showing an Audit to see if the formula is working:

 

This shows all Invoices for MYK (showing correct conversion from MYR to USD):

MYR Example is Correct.jpg

 

This shows all Invoices for VND Branch (showing incorrect conversion from VND to USD):

VND Example is Incorrect.jpg

 

Help would be greatly appreciated.

 

Microsoft should develop a DAX expression that would make it much easier to manage Forex Rate Tables with missing dates (as weekends are always missing).

1 ACCEPTED SOLUTION

Accepted Solutions
JRHans09 Regular Visitor
Regular Visitor

Re: Convert multiple currencies to USD using multiple currency Fx Table with missing weekend/other d

Sam, thank you so much for your help. It is a beautiful solution and I have learned about TOPN, now. I can apply this logic to other types of calculations, as well - top X Sales, Customers, etc. Super helpful. Thanks.

3 REPLIES 3
Community Support Team
Community Support Team

Re: Convert multiple currencies to USD using multiple currency Fx Table with missing weekend/other d

@JRHans09,

 

You may take a look at the post below.

https://community.powerbi.com/t5/Desktop/Find-last-value-based-on-date/m-p/293150#M129228

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
JRHans09 Regular Visitor
Regular Visitor

Re: Convert multiple currencies to USD using multiple currency Fx Table with missing weekend/other d

Sam, thank you so much for your help. It is a beautiful solution and I have learned about TOPN, now. I can apply this logic to other types of calculations, as well - top X Sales, Customers, etc. Super helpful. Thanks.

JRHans09 Regular Visitor
Regular Visitor

Re: Convert multiple currencies to USD using multiple currency Fx Table with missing weekend/other d

For ease of access for other users, I am reposting your code here:

Column =
IF (
    'TRANSACTION'[CURRENCY] = "EUR",
    1,
    MAXX (
        TOPN (
            1,
            FILTER (
                EXCH_RATE,
                EXCH_RATE[CURRENCY] = 'TRANSACTION'[CURRENCY]
                    && EXCH_RATE[DATE_FROM] <= 'TRANSACTION'[PAYMENT_DATE]
            ),
            EXCH_RATE[DATE_FROM], DESC
        ),
        EXCH_RATE[EXCH_RATE]
    )
)

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 42 members 997 guests
Please welcome our newest community members: