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

[JRHans09]

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:

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):

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

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

[v-chuncz-msft]

@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

[JRHans09 · Accepted solution]

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]

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]
    )
)