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

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.