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

        d_gosbell jdbuchanan71 

        Expected values are goods ( columns D, E are precalculated for example: YTD Sales for Djaber country in february equals 3000=[ January 1000 + February 2000])

         

        For the formula i can't do anything. Its imposed ;(  , i'll gonna be crazy 

        To precise the problem or the issue: the problem is that i can't use ytd of previous period inside sumx() 

         

        Any one have a suggestion.

         

         

  • dkozak's avatar
    dkozak
    Frequent Visitor
    6 years ago

    jdbuchanan71  The caption was for sales of february