Forum Discussion

dkozak's avatar
dkozak
Frequent Visitor
7 years ago

Currency conversion Issue

Hi everyone, 

I have complex formula to calculate the total sales in euro currency. 

The formula is as follows: 

Sales(Euro) = calculate( divide(YTD Sales Month; Exchange rate Month) - Divide( YTD Sales Month - 1 ; Exchange rate Month -1)) Exampl for Sales (Euro)of February= calculate(Divide( YTD Sales february ; Exchange rate February) - Divide( YTD Sales Janyary ; Exchange Rate January))

And for that i have three tables Sales_Table, CurrencyExchange_Table(date, local currency and value in euro) and Calendar Table. How you'r modeling this situation??!! (for me it gives me wrong total in case of sample formula as described or wrong results when i use Sumx and previous YTD)

 

Could anyone Help me Please!

Need Help

4 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi dkozak 

    Assume "euro value" in my test file refers to value in euro per local currency unit.

    So we could consider "euro value" as Exchange rate.

     

    1.create two columns

    connected_cl1 = [date]&[local currency]
    # in rate table
    connected_cl2 = [date]&[country]
    #in sales table

    2. create relationship between "sales table" and "rate table"

     

    3. to get total sales in euro currency per month in a year

    add [year],[month] from "calendar" table into the matrix "row" field,

    create a measure and add it into "value" field of the matrix.

    euro sales = MAX(sales[sales value])*MAX('rate table'[euro value])

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • dkozak's avatar
      dkozak
      Frequent Visitor
      7 years ago

      Hi v-juanli-msft 

      I did it before and it did not work, it gives us a wrong totals. 

      Any one have an idea ?

      The problem is bigger :/ 

      I cant add my pbix to this post llike you did.

      Regards

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        7 years ago

        Hi dkozak 

        Remove Private information from your file,

        Upload your file to Onedrive,

        Share the link with me

         

        Best Regards
        Maggie