Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Shanze
Frequent Visitor

Currency Conversion Power Bi

Hi All,

 

This might be a very basic question to many of you. I am trying to create a model where all my transactions carried out in different currencies is translated into USD using montly exchange rates.

 

My model has three tables,  1) calendar (marked as date table), 2) fact_transaction table (this table has transaction values as well as transation date) and 3) exchange rate table (containing monthly average exchange rates on 1st date of each month against multiple currencies).

 

Relationship is direct between calendar and transaction date as well as calendar and monthly exchange rate date.

 

Help to bring clarity would be appreciated.

1 ACCEPTED SOLUTION

You could do it as a measure like

USD Amount Measure =
SUMX (
    'Transactions',
    VAR CurrencyRate =
        LOOKUPVALUE (
            'Exch rate'[rate],
            'Exch Rate'[Date],
                DATE ( YEAR ( 'Transactions'[Transaction date] ), MONTH ( 'Transactions'[Transaction date] ), 1 ),
            'Exch rate'[Currency], 'Transactions'[Currency]
        )
    RETURN
        'Transactions'[Amount] * CurrencyRate
)

but I think performance would likely be better having a calculated column and then building measures based on that. If it was a column then it would be calculated once, during data refresh, and then you could have measures to perform sum, average etc on the calculated column without having to pay the cost of all the lookups.

Best advice is to try both ways and use DAX Studio to compare performance

View solution in original post

4 REPLIES 4
Shanze
Frequent Visitor

@johnt75 - thank you so much. Both methods worked. 

johnt75
Super User
Super User

You could create a calculated column in the transactions table like

USD Amount =
VAR CurrencyRate =
    LOOKUPVALUE (
        'Exch rate'[rate],
        'Exch Rate'[Date],
            DATE ( YEAR ( 'Transactions'[Transaction date] ), MONTH ( 'Transactions'[Transaction date] ), 1 ),
        'Exch rate'[Currency], 'Transactions'[Currency]
    )
RETURN
    'Transactions'[Amount] * CurrencyRate

Hi John,

 

This did work. But i was wondering if instead of adding conditional column, there was a way to achieve this through dax measures.

 

Since i intend to add more factual transaction table and as far as i know measures are way better in terms of performance than calculated columns.

 

You could do it as a measure like

USD Amount Measure =
SUMX (
    'Transactions',
    VAR CurrencyRate =
        LOOKUPVALUE (
            'Exch rate'[rate],
            'Exch Rate'[Date],
                DATE ( YEAR ( 'Transactions'[Transaction date] ), MONTH ( 'Transactions'[Transaction date] ), 1 ),
            'Exch rate'[Currency], 'Transactions'[Currency]
        )
    RETURN
        'Transactions'[Amount] * CurrencyRate
)

but I think performance would likely be better having a calculated column and then building measures based on that. If it was a column then it would be calculated once, during data refresh, and then you could have measures to perform sum, average etc on the calculated column without having to pay the cost of all the lookups.

Best advice is to try both ways and use DAX Studio to compare performance

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors