Exchange Rate Conversion with Date Selection (Slicer)

[PBI-Enthusiast]

Hi

I want to be able to convert values based on exchange rates (Multi-currency to Single-currency) via a date selection with a slicer:

The closest I got is the above example where I can select a date and the value of each row (by Company) is correct for calculated column "Value Converted" and most important - the Total is correct as well (must be the sum of the rows).

This is currently working because I'm using a hardcoded Date for the calculated column "Value Converted":

Value Converted =
VAR vRowCurrency = SalesFact[CurrencyAccounting]
VAR vRowDate = DATEVALUE("2023/01/02")
VAR vRowValue = SalesFact[Value]
VAR vExchangeRate =
    CALCULATE(
        MAX( ExchangeRate[ExchangeRate] ),
        ALL( ExchangeRate ),
        ExchangeRate[CurrencyFrom] = vRowCurrency
            && ExchangeRate[ExchangeDate] = vRowDate
    )
RETURN
    vRowValue * vExchangeRate

But I'm not able to make this dynamic, so that the selected date is used - I've tried something like this, but it didn't work:

VAR vRowDate = MAX('Calendar'[Date])

This is the current data model:

I've used a bridge table to prevent a many-to-many relationship between ExchangeRate and SalesFact - by using the sid (combination of ExchangeDate & CurrencyFrom) the values are unique.

SalesFact:

Bridge:

ExchangeRate:

Calendar:

Any hint how I can achieve my goal would be welcome.

Thanks!
PBI-Enthusiast

[amustafa · Accepted solution]

OK, you asked for it 🙂 I have updated the .pbix file in my shared Google Drive. No need to link your Calendar table to Sales table. Here's the updated DAX measure to calculate sales in USD from currencies you have in the Sales table.

Google Drive link: https://drive.google.com/drive/folders/1v0nMgmbXVJLINH-56930In51WaBQrmvJ?usp=sharing

Sales (USD) =

VAR SelectedStartDate = MIN(Calendar[Date])

VAR SelectedEndDate = MAX(Calendar[Date])

VAR AvgExchangeRateCAD = CALCULATE(AVERAGE('XE Rates'[CAD]), 'XE Rates'[Date] >= SelectedStartDate, 'XE Rates'[Date] <= SelectedEndDate)

VAR AvgExchangeRateEUR = CALCULATE(AVERAGE('XE Rates'[EUR]), 'XE Rates'[Date] >= SelectedStartDate, 'XE Rates'[Date] <= SelectedEndDate)

VAR AvgExchangeRateCHF = CALCULATE(AVERAGE('XE Rates'[CHF]), 'XE Rates'[Date] >= SelectedStartDate, 'XE Rates'[Date] <= SelectedEndDate)

RETURN

    IF(

        HASONEVALUE(Sales[Currency]),

        SWITCH(

            SELECTEDVALUE(Sales[Currency]),

            "CAD", [Total Sales] / AvgExchangeRateCAD,

            "EUR", [Total Sales] / AvgExchangeRateEUR,

            "CHF", [Total Sales] / AvgExchangeRateCHF,

            "USD", [Total Sales],

            BLANK()

        ),

        SUMX(

            VALUES(Sales[Currency]),

            VAR CurrentCurrency = Sales[Currency]

            VAR CurrentSales = CALCULATE([Total Sales], Sales[Currency] = CurrentCurrency)

            RETURN

                SWITCH(

                    CurrentCurrency,

                    "CAD", CurrentSales / AvgExchangeRateCAD,

                    "EUR", CurrentSales / AvgExchangeRateEUR,

                    "CHF", CurrentSales / AvgExchangeRateCHF,

                    "USD", CurrentSales,

                    BLANK()

                )

        )

    )

[PBI-Enthusiast]

Hi @amustafa 

Thank you very much for the provided solution, that's exaclty what I was looking for 🙂

Best regards
PBI-Enthusiast

[amustafa]

@PBI-Enthusiast Your sales are related to some time period (month, year) right? You can take the average currency rate for a month or a year form XE Rate table then do the convertion. Else, the converted curency rate is subjective and not trustworthy. 

[PBI-Enthusiast]

@amustafa I totally agree that in general it makes only sense with a related date within the fact table to be able to get the correct XE Rate. But in my special case, the customer wants to simulate all the values of the entire sales table for different XE Rate dates. It's not relevant when the sales / transactions actually happens, important is only the date of the XE Rate on the specific XE Dates.

[amustafa]

Hi @PBI-Enthusiast Look at the solution in my Google Drive.

https://drive.google.com/drive/folders/1v0nMgmbXVJLINH-56930In51WaBQrmvJ?usp=sharing

If I answered your question, please mark this thread as accepted and Thums Up!
Follow me on LinkedIn:
https://www.linkedin.com/in/mustafa-ali-70133451/

[PBI-Enthusiast]

Hi @amustafa 

Thank you for the suggested solution.

In general that would work, but I don't have any date column in my Sales table which is relevant in this case. The column from table ExchangeRate[ExchangeDate] is only related to the Calendar[Date]. I could also use ExchangeRate[ExchangeDate] in my date slicer directly, which would eliminate the Calendar table to make the model less complex.

I tried to replace this line:

VAR SaleDate = Sales[Sales Date]

With this line:

VAR SaleDate = MAX('Calendar'[Date])

But it didn't work as expected.

Maybe one solution would be to redesign my data model, but I don't know how it should look like.

[amitchandak]

@PBI-Enthusiast , You need a measure like

Value Converted =
VAR vRowCurrency = SalesFact[CurrencyAccounting]
VAR vRowDate = maxx(allselected(Calendar), Calendar[Date]) // or use max(Calendar[Date])
VAR vRowValue = SalesFact[Value]
VAR vExchangeRate =
CALCULATE(
MAX( ExchangeRate[ExchangeRate] ),
ALL( ExchangeRate ),
ExchangeRate[CurrencyFrom] = vRowCurrency
&& ExchangeRate[ExchangeDate] = vRowDate
)
RETURN
vRowValue * vExchangeRate

[PBI-Enthusiast]

Thank you for the suggested solution.

Should these be used as a measure or a calculated column?

If I'm trying to create a measure, these lines are invalid without any aggregation (A single value for column 'CurrencyAccounting' in table 'SalesFact' cannot be determined... )

• VAR vRowCurrency = SalesFact[CurrencyAccounting]
• VAR vRowValue = SalesFact[Value]

If I'm trying to create a calculated column, there's no syntax error, but the return value is blank:

This variable (both variants) doesn't seem to return the expected date selected:

VAR vRowDate = maxx(allselected(Calendar), Calendar[Date]) // or use max(Calendar[Date])

When I'm replacing this with the hardcoded date, it would work:

VAR vRowDate = DATEVALUE("2023/01/02")

Any idea, how this can be fixed?

Best regards
PBI-Enthusiast