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()
                    )
            )
        )
     
amustafa's avatar
amustafa
Icon for Solution Sage rankSolution Sage
2 years ago

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

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.