Forum Discussion

dkozak's avatar
dkozak
Frequent Visitor
6 years ago

Currency conversion issue Sumx Issue

Hello community, Need help please, i'm atually working on a report who present for us the sales value by different slicers (country, category, date). what we have as an existing is two files on dri...
Need Help
jdbuchanan71's avatar
jdbuchanan71
Super User
6 years ago

Hello dkozak 

You already have the relationship between Sales and Rates Table using Key-Link-Sales so your measure can just use that.

Sales Amount € = SUMX ( Sales, Sales[Sales Value] * RELATED ( 'Rates Table'[Value] ) )

dkozak's avatar
dkozak
Frequent Visitor
6 years ago

Hello jdbuchanan71 , but the rule of conversion is different.

The rule or the formula of calulation is like: 

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

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    6 years ago

    dkozak 

    Not sure I am following your example formulas.  Given your sample data, could you fill in the last 2 columns in this table?

    Country Date  Sales Amount  Value Expected_Result Formula
    RUSSIA 1/1/2019 28,325,732 76.3054545    
    RUSSIA 2/1/2019 10,624,860 75.5497    
    RUSSIA 3/1/2019 24,620,460 74.9093921    
    RUSSIA 4/1/2019 15,480,003      
    RUSSIA 5/1/2019 3,880,002      
    RUSSIA 6/1/2019 6,480,002      
    RUSSIA 7/1/2019 6,480,002      
    RUSSIA 8/1/2019 10,480,002      
    RUSSIA 9/1/2019 16,880,002      
    RUSSIA 10/1/2019 1,800,002      
    RUSSIA 11/1/2019 1,800,002      
    RUSSIA 12/1/2019 19,306,110      
    • dkozak's avatar
      dkozak
      Frequent Visitor
      6 years ago

      jdbuchanan71 

      you will find bellow a sample calculation of what is expected as result 

      • d_gosbell's avatar
        d_gosbell
        Super User
        6 years ago

        I think your expected result is incorrect.

         

        Mathematically "YTD Feb" = (Jan Sales + Feb Sales). So doing "YTD Feb" / "Feb Rate" is the same as:

        Jan Sales / Feb Rate
        + Feb Sales  / Feb Rate

         

        so you are doing: 

         

        Jan Sales / Feb Rate
        + Feb Sales  / Feb Rate

        - Jan Sales / Jan Rate

         

        Which is applying both Jan and Feb rates to the Jan Sales. I cannot see logically how this could be valid.

         

        The original approach suggested by jdbuchanan71 is the typical pattern that would be used in a currency conversion situation. I don't think you can use the RELATED() pattern as you need to look up the rate for a period and country, but you could use a LOOKUPVALUE() expression instead to achieve this.

    • dkozak's avatar
      dkozak
      Frequent Visitor
      6 years ago

      jdbuchanan71  The caption was for sales of february