Forum Discussion

Rdarshana's avatar
Rdarshana
Icon for Helper II rankHelper II
1 year ago

Currency Conversion Data Model

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 ...
Need Help
Tips and Tricks
Rdarshana's avatar
Rdarshana
Icon for Helper II rankHelper II
1 year ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.