Forum Discussion

PBI-Enthusiast's avatar
PBI-Enthusiast
Helper 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
Solution Sage
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()
                )
        )
    )
 
PBI-Enthusiast's avatar
PBI-Enthusiast
Helper I
2 years ago

Hi amustafa 

 

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

 

Best regards
PBI-Enthusiast