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

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.