Currency Conversion Data Model

[Rdarshana]

Hi,
I wanted to run the data model currency conversion for the group out here.

This is what I have. The dimensions are all Dual Mode and Fact tables are Direct Query.

Exchange Rates are recorded at the first day of month and rate code

So, the Exchange Rate table is joined to the Calendar table on Rate Date == First Day of Month.

Exchnage Rate table is joined to both Fact 1 and Fact 2 on Currency Code. 

What we need:
We will need to calculate the measures (Amount 1, Amount 2...) based on the currency code selected - In this case, it will be the To Currency in the exchange rate table. 

My question - I am looking into DAX query calcs, and see that merges being suggested in forums. 
I only want to levergae the calc based on the To Currency Code selected by the user. 

Is there a cleaner way to do accomplish that>?

[Rdarshana]

 Hi @v-tangjie-msft ,
Thank you for helping with the DAX query.
However, that DAX query did not help me. 

I am posting the query that worked for me, incase it helps someone. 

Synopsis: 

Financials Fact is joined to Calendar table, and data is stored at a daily level.
Exchange Rate table is not joined to any table in this data model. The rates are stored in a monthly level. 

New Spend 9 =

VAR ConversionStartDate = MIN('Calendar'[Date])  -- Start date from slicer

VAR ConversionEndDate = MAX('Calendar'[Date])    -- End date from slicer

VAR LocalCurrencyCode = SELECTEDVALUE('Financials Fact'[Currency Code])  -- Local currency in Sales table

VAR TargetCurrencyCode = SELECTEDVALUE(exchange_rate[To Currency])  -- Target currency from slicer

-- Lookup the exchange rate within the selected date range

VAR ConvertedSales =

    SUMX(

        FILTER(

            'Financials Fact',

            'Financials Fact'[Date]>= ConversionStartDate && 'Financials Fact'[Date] <= ConversionEndDate

        ),

        'Financials Fact'[Sale Amount] *

        LOOKUPVALUE(

            'exchange_rate'[Rate],

            'exchange_rate'[From Currency], LocalCurrencyCode,

            'exchange_rate'[To Currency], TargetCurrencyCode,

            'exchange_rate'[Rate Date],EOMONTH('Financials Fact'[Date], -1) + 1 -- Rate Date is only at monthly level

        )

    )

RETURN

    IF(ISBLANK(ConvertedSales), SUM('Financials Fact'[Sale Amount]), ConvertedSales)

[Rdarshana]

@v-tangjie-msft 
Hi Neeko,

I tried a couple of other alternatives based on my above post. 
So, I have an exchange rate table, that I do not want to attach to the fact or calendar date table yet. 

My report has a requirement of a date slider and To Currency. The amount should be converted for that month in the date range slider such that -  month 1, it will be sales amount * exchange rate for that month , added to next month 2, sales amount * exchange rate for that second month and so on. 

In the attached file you will see, I created two Calculated Sales measures.

Calculated Sales -

Calculated SALES =

VAR ConversionStartDate = MIN('Calendar Date'[Date])  -- Start date from slicer

VAR ConversionEndDate = MAX('Calendar Date'[Date])    -- End date from slicer

VAR LocalCurrencyCode = SELECTEDVALUE('financials'[CurrencyCode])  -- Local currency in Sales table

VAR TargetCurrencyCode = SELECTEDVALUE('Exchange Rate'[To Currency])  -- Target currency from slicer

-- Lookup the exchange rate within the selected date range

VAR ExchangeRateValue =

    CALCULATE(

        MAX('Exchange Rate'[Rate]),  -- Or use AVERAGE if needed

        'Exchange Rate'[From Currency] = LocalCurrencyCode,

        'Exchange Rate'[To Currency] = TargetCurrencyCode,

        'Exchange Rate'[Rate Date] >= ConversionStartDate && 'Exchange Rate'[Rate Date] <= ConversionEndDate

    )

RETURN

    IF(ISBLANK(sum(financials[Gross Sales]) *ExchangeRateValue), sum(financials[Gross Sales]), (sum(financials[Gross Sales])*ExchangeRateValue))

I need to modify this query such that in the Calculate Function, I need to pick the correct Exchange Rate for that month, and not the MAX.
As you can see, for the date slider range, the Calculated Sales amount is picking up the max exchnage rate for November and calculating the amount based on this rate. This is not what I want. It should use the rate for the month that is provided. 

In addition, I created another Calculated Sales 2 measure that creates a date table based on the date filter selected, and creates a start of month. (In this example, I used the first day of month in the fact table, but this is not the case, we have all dates).

Calculated SALES 2 =

VAR ConversionStartDate = MIN('Calendar Date'[Date])  -- Start date from slicer

VAR ConversionEndDate = MAX('Calendar Date'[Date])    -- End date from slicer

VAR LocalCurrencyCode = SELECTEDVALUE('financials'[CurrencyCode])  -- Local currency from Sales table

VAR TargetCurrencyCode = SELECTEDVALUE('Exchange Rate'[To Currency])  -- Target currency from slicer

-- Create a table of all months between the start and end date

VAR MonthsInRange =

    ADDCOLUMNS(

        CALENDAR(ConversionStartDate, ConversionEndDate),  -- Create a calendar table between selected dates

        "StartOfMonth", EOMONTH([Date], -1) + 1  -- Calculate the start of the month for each date

    )

-- Calculate the converted amount for each month by looking up the exchange rate for the start of the month

VAR ConvertedAmountByMonth =

    SUMX(

        MonthsInRange,

        VAR _StartOfMonth = [StartOfMonth]  -- Get the start of the month from the generated table

        VAR ExchangeRateValue = LOOKUPVALUE(

            'Exchange Rate'[Rate],

            'Exchange Rate'[From Currency], LocalCurrencyCode,

            'Exchange Rate'[To Currency], TargetCurrencyCode,

            'Exchange Rate'[Rate Date], _StartOfMonth

        )

        VAR SalesAmountForMonth = CALCULATE(

            SUM('financials'[Gross Sales]),

            FILTER('financials',

                YEAR('financials'[Date]) = YEAR([Date]) &&

                MONTH('financials'[Date]) = MONTH([Date])

            )  -- Filter sales by the year and month of the current row in MonthsInRange

        )

        RETURN IF(

            NOT(ISBLANK(ExchangeRateValue)),

            SalesAmountForMonth * ExchangeRateValue,  -- Multiply the sales amount by the exchange rate

            BLANK()  -- Handle missing exchange rates

        )

    )

RETURN ConvertedAmountByMonth

I am unable to add the pbix file. 

Let me know if this helps.

[v-tangjie-msft]

Hi @Rdarshana ,

1.Based on your description, I created a date table. You can then refer to the second reply to create a currency slicer table where you can modify the DAX code yourself to add your currency options.

Slicer = DATATABLE ( 
    "Currency", STRING, 
    {
        { "USD" },
        { "MKD" }
    }
) 

DATATABLE function - DAX | Microsoft Learn

2. Create measures.

Measure = 
var _select_currency=SELECTEDVALUE('Slicer'[Currency])
var _rate=CALCULATE(SUM('Exchnage Rate table'[Rate]),FILTER(ALL('Exchnage Rate table'),MONTH([Rate Date])=MONTH(MAX('Fact 1'[Date])) && [From Currency]=MAX('Fact 1'[Currency Code])))
var _amount=SUM('Fact 1'[Amount])
RETURN IF(_select_currency=MAX('Fact 1'[Currency Code]),_amount,_rate* _amount)

To currency = IF(MAX('Fact 1'[Date]) in VALUES('CALENDAR'[Date]),SELECTEDVALUE('Slicer'[Currency]),BLANK())

Please refer my pbix file.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Rdarshana]

@v-tangjie-msft  I am checking to see if the above solution works.

[Rdarshana]

Hi Neeko,
I looked at the DAX calc too, but there seems to be an issue with the model.

The relationship between Exchange Rate table and Fact 2 and Fact Table 1 tables is inactive as PBI identifies it as many-to-many relationship and presence of ambiguous relationships as Exchange rate table and Calendar tables are both joined to the fact tables. 

So, let's say, we decide not to join the Exchange Rate table at all. 
I am referencing this link - https://www.youtube.com/watch?v=HlR-AyzctLY

I can create a measure using LookUp function - 
** I will have a currency slicer
** I have a calendar date slicer . The max date on this slicer should give me the first of month for that date.
and this first of month should give me the exchange rate

Measure = 

VAR CurrencySelected = SELECTEDVALUE(ExchangeRate[To Currency])  ** This will be the currency slicer**

VAR RateDate = LASTDATE('Calendar'[Date])                               ** This is max date from slicer; I will get the first of month from this date and calc the rate from the exchange rate table based on first of month**

VAR RECENTRATE =

LOOKUPVALUE(monthly_average_exchange_rate[Rate],

monthly_average_exchange_rate[To Currency],CurrencySelected,

monthly_average_exchange_rate[Rate Date],STARTOFMONTH(RateDate))

RETURN

IF(ISBLANK([Total Software Spend]*RECENTRATE),[Total Software Spend],[Total Software Spend]*RECENTRATE)

The above DAX query works when I select the ToCurrency Code to be anything else other than USD. 
If I select USD, then it gives me an error stating - "Couldnt load data for this visual. A table of multiple values was supplied where a single value was expected"

I added the IsBlank condition just to ensure that the data will show the original amount as is in the New amount field. 
Is there anything amiss? 

[v-tangjie-msft]

Hi @Rdarshana ,

According to your description, here are my steps you can follow as a solution.

(1) We can create a slicer table.

Slicer = DATATABLE ( 
    "Currency", STRING, 
    {
        { "USD" },
        { "MKD" }
    }
) 

(2) We can create a measure. 

Measure = 
var _select_currency=SELECTEDVALUE('Slicer'[Currency])
var _rate=CALCULATE(SUM('Exchnage Rate table'[Rate]),FILTER(ALL('Exchnage Rate table'),MONTH([Rate Date])=MONTH(MAX('Fact 1'[Date])) && [From Currency]=MAX('Fact 1'[Currency Code])))
var _amount=SUM('Fact 1'[Amount])
RETURN IF(_select_currency=MAX('Fact 1'[Currency Code]),_amount,_rate* _amount)

Measure 2 = 
var _select_currency=SELECTEDVALUE('Slicer'[Currency])
var _rate=CALCULATE(SUM('Exchnage Rate table'[Rate]),FILTER(ALL('Exchnage Rate table'),MONTH([Rate Date])=MONTH(MAX('Fact 1'[Date])) && [From Currency]=MAX('Fact 2'[Currency Code])))
var _amount=SUM('Fact 2'[Amount])
RETURN IF(_select_currency=MAX('Fact 2'[Currency Code]),_amount,_rate* _amount)

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.