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 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

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()
                    )
            )
        )
     

8 Replies

  • amitchandak's avatar
    amitchandak
    Super 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
      Helper 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
    • 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.

  • amustafa's avatar
    amustafa
    Solution 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
      Helper 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.

  • 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