Forum Discussion

PBI-Enthusiast's avatar
PBI-Enthusiast
Icon for Helper I rankHelper I
2 years ago
Solved

Exchange Rate Conversion with Date Selection (Slicer)

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 ...
Need Help
  • amustafa's avatar
    amustafa
    2 years ago

    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()
                    )
            )
        )
     
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
2 years ago

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's avatar
PBI-Enthusiast
Icon for Helper I rankHelper I
2 years ago

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