DAX Workout 021 - Using DAX to join tables (currency exchange rate calculations)

[tweinzapfel]

Everyone,

This workout is all about creating a DAX measure to calculate USD sales amounts . You are provided a data set that includes a snapshot of sales data during the first few days of January 2014 as well as a table of currency exchange rates that covers the period.

This workout comes from an excellent example in the book, “Expert Data Modeling with Power BI” written by Soheil Bakhshi and published by Packt Publishing. (Link to Amazon is provided for reference)

The reason that I wanted to use this example is that it highlights a very common scenario where you need to join data from tables using more than one field. In this example, it involves both currency codes as well as dates. However, the standard relationship joins in Power BI only allow one field to be used.

Here is the data:
Workout 021 Data.xlsx (45.0 KB)

Submission
Load the supplied data file, create your solution, and reply to this post. Upload a screenshot of your solution (or the PBI file) along with any DAX items. Please blur any solution or place it in a hidden section.

[kumariginka19]

Dear Enterprise DNA ,
This is my first submission of DAX workouts
Here is the DAX Code , I had used

Lookup the values of average rate according to the currency rate and ship Date to Internet Sales table

measure to calculate the Sales Amount

here is the result

Thank you so much Enterprise DNA for this wonderful platform .

[HufferD]

@tweinzapfel,

This is the image i meant to paste earlier:

By converting InternetSales[SalesAmount] to USD by looking up the CurrencyKey for the specific date using an inner join between Internet Sales and Currency Rates on CurrencyKey AND Date:

Summary

Sales (USD) = 
SUMX (
    NATURALINNERJOIN ( 
        SELECTCOLUMNS (  
            'Internet Sales'
            , "@Key" , 'Internet Sales'[CurrencyKey] * 1
            , "@Date", 'Internet Sales'[Ship Date] * 1
            , "@SalesAmount", 'Internet Sales'[SalesAmount]
        ) 
        , SELECTCOLUMNS (
            'Currency Rate'
            , "@Key" , 'Currency Rate'[CurrencyKey] * 1
            , "@Date" , 'Currency Rate'[Date] * 1 
            , "@AverageRate" , 'Currency Rate'[Average Rate]
        )
    ) 
    , [@AverageRate] * [@SalesAmount]
)

[shahadathossen.shajj]

DAX Measure

This text will be hidden

Rate =
CALCULATE (
    MAX ( 'Currency Rate'[AverageRate] ),
    TREATAS (
        SUMMARIZE (
            'Internet Sales',
            'Internet Sales'[CurrencyKey],
            'Internet Sales'[ShipDate]
        ),
        'Currency Rate'[CurrencyKey],
        'Currency Rate'[Date]
    )
)

USD Sales $ =
SUMX ( 'Internet Sales', [Rate] * 'Internet Sales'[SalesAmount] )

[Eze]

Here is my submission, @tweinzapfel thanks for the workout

DAX Code

Summary

[EstherTesar]

DAX workout 021 Currency conversion.pbix (33.1 KB)

[BrianJ]

Nothing fancy here…

DAX Measures

Rate =
MAXX (
    FILTER (
        ALL ( 'Currency Rate' ),
        'Currency Rate'[CurrencyKey] = SELECTEDVALUE ( 'Internet Sales'[CurrencyKey] )
            && 'Currency Rate'[Date] = SELECTEDVALUE ( 'Internet Sales'[ShipDate] )
    ),
    'Currency Rate'[AverageRate]
)

USD Sales Amount = 
SUMX(
    'Internet Sales',
    'Internet Sales'[SalesAmount] * [Rate]
)

• Brian

[AlexisOlson]

A simple approach:

DAX Code

USD Sales Amount =
SUMX (
    'Internet Sales',
    'Internet Sales'[SalesAmount]
        * LOOKUPVALUE (
            'Currency Rate'[AverageRate],
            'Currency Rate'[CurrencyKey], 'Internet Sales'[CurrencyKey],
            'Currency Rate'[Date], 'Internet Sales'[ShipDate]
        )
)