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

  • Sales file: contain sales by country, month, category..etc ( in local monney)
  • Sales Rate Table: Contain rates change by local currency and date (three column: local currency, date, value)

Link to sources and PBix drive: https://1drv.ms/u/s!Ajh-Pr8pM0w_bG6WApK

The need is getting this sales values in the euro currency (all rates table reference to 1 euro of value) and using this formula :

Sales Euro Month=  divide(total sales YTD Month; Currency Exchange Rate Month) - divide(total sales YTD Month-1; Currency Exchange Rate Month-1)

Example: Sales Euro February =  divide(total sales YTD February; Currency Exchange Rate February) - divide(total sales YTD January; Currency Exchange Rate January).

The problem is that i can't get the wright values using the following dax formula: 

  • calculate(sumx( sales; divide(totalytd(sum(sales value); date); min(ratevalue) - calculate(divide(totalytd(sum(sales value); date); min(ratevalue); previousMonth(date))))

after investigating i found that this formula returns to me just the first part of calculation befor substraction [divide(totalytd(sum(sales value); date); min(ratevalue)]

Have an Idea please ? How can i get the wright values 

 

The link bellow contain the fils source and the pbix application( contains the modal)

https://1drv.ms/u/s!Ajh-Pr8pM0w_bG6WApK-LFWvwTU?e=F0I0Py

At the end i want to mention the supper users may be they can have a sollution for this Big Problem

jdbuchanan71  Greg_Deckler  Zubair_Muhammad Cmcmahan parry2k Ashish_Mathur AlB MFelix  TomMartens Mariusz Anonymous d_gosbell Anonymous 

let me know if you need any more informations

Thanks in advance

 

 

 

Need Help

7 Replies

  • 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