Currency conversion issue Sumx Issue

[dkozak]

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

[jdbuchanan71]

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]

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]

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

@jdbuchanan71  The caption was for sales of february

[dkozak]

@jdbuchanan71 

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

[d_gosbell]

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]

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