Forum Discussion

khaeshr46's avatar
khaeshr46
Frequent Visitor
2 years ago
Solved

Constant Currency dynamic calculation help needed

Hi, I need to create a dynamic Constant exchange calculation for 3 prior years using max rates of the currently selected Quarter Year exchange rates. I have all my sales in Local Currency.    ...
  • lbendlin's avatar
    lbendlin
    2 years ago

     

    PY Sales USD = 
var a = summarize('Fact',[Currency],[Date])
var b = ADDCOLUMNS(a,"fx",CALCULATE(max('Currency'[Fx Rate to USD]),TREATAS({[Date]},'Currency'[Date]),treatas({[Currency]},'Currency'[Currency])))
var c = ADDCOLUMNS(b,"p",[PY Sales LC])
return sumx(c,[fx]*[p])

     

khaeshr46's avatar
khaeshr46
Frequent Visitor
2 years ago

Hi,

Below is some sample data and a new screenshot below of what is required.

 

PY Sales USD measure is calculating correctly but any suggestion to make the measure perform faster would be appriciated.

 

PY Sales USD Latest Month Rate is not working.

Also, if I would like to create a PY Full Year Sales USD as well using the selected quarter's exchange rates.

 

Currency Table

DateCurrencyFx Rate to USD
07/31/13USD1.00000
07/31/13CAD1.02870
08/31/13EUR1.32350
08/31/13USD1.00000
08/31/13CAD1.05530
09/30/13CAD1.02850
09/30/13EUR1.35050
09/30/13GBP1.61530
09/30/13USD1.00000

 

Fact Table

DateLC AmountCurrencyProductID
07/31/12  96,444,348.00USD113
07/31/12     272,570,350.80CAD122
07/31/12        41,111,070.00USD129
08/31/12     153,148,122.70CAD122
08/31/12        54,166,612.50USD129
09/30/12     352,368,933.70CAD122
09/30/12     290,977,531.20USD113
09/30/12        41,388,847.50USD129
07/31/13        41,772,180.45USD129
07/31/13     466,478,260.70CAD122
08/31/13        65,741,175.92EUR157
08/31/13        66,666,600.00USD113
08/31/13        25,972,196.25USD129
08/31/13     378,041,253.10CAD122
09/30/13        14,922,207.30USD129
09/30/13     281,268,319.20CAD122
09/30/13        58,975,793.59EUR157
09/30/13        94,719,152.48GBP176
09/30/13        66,666,600.00USD113

 

Measures

PY Sales USD =
VAR vSalesWithFXRate =
    ADDCOLUMNS (
        SUMMARIZE (
            Sales,
            'Calendar_Lookup'[Date],
            'Currency'[Currency]
        ),
        "_LC Amount", [PY Sales LC],
        "_FX Rate to USD", CALCULATE (
            SELECTEDVALUE ( 'FX Rate'[Fx Rate to USD])
        )
    )

RETURN
    SUMX (
        vSalesWithFXRate,
        [_LC Amount] / [_FX Rate to USD]
    )
PY Sales USD Latest Month Rate =
var ccDate = CALCULATE(
        MAX('FX Rate'[Date]),
        ALLSELECTED('Calendar_Lookup'[Date])
)
VAR vSalesWithConstantRate =
SUMMARIZE(
    Sales,
    'Currency'[Currency],
    "_LC Amount",
    [PY Sales LC],
    "_Constant Rate",
    CALCULATE(
        MAX('FX Rate'[Fx Rate to USD]),
        FILTER(
            ALL('FX Rate'),
            'FX Rate'[Date] = ccDate
            && 'FX Rate'[Currency] = SELECTEDVALUE('Currency'[Currency])
        )
    )
)
RETURN
SUMX(vSalesWithConstantRate, [_LC Amount]/[_Constant Rate])
 

 

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

Here is what I have so far. Please have a look at my approach. I guess I don't understand this part:

 

using max rates of the currently selected Quarter

 

The measures can be adjusted to the last available FX, for example.

  • khaeshr46's avatar
    khaeshr46
    Frequent Visitor
    2 years ago
    Hi,
    The TREATAS works well at row context. But the total was incorrect so i wrapped it in a SUMX SUMMARIZE for Sales USD measure and its giving me the right total. (Sales USD Correct Total)
     
    The PY Sales USD however does not work with SUMX SUMMARIZE. (PY Sales USD Correct Total)
     
    Sorry I was a bit unclear on the "using max rates of the currently selected Quarter" requirement. What I am looking to do is use the max date's FX rates of a selected Quarter. Please see the screenshot below. 
     
     
    Sales USD Correct Total = SUMX(SUMMARIZE('Fact','Fact'[Date],'Fact'[Currency],"A",sum('Fact'[LC Amount])*CALCULATE(max('Currency'[Fx Rate to USD]),TREATAS(values('Fact'[Date]),'Currency'[Date]),treatas(values('Fact'[Currency]),'Currency'[Currency]))),[A])
     
    PY Sales USD Correct Total = SUMX(SUMMARIZE('Fact','Fact'[Date],'Fact'[Currency],"A",[PY Sales LC]*CALCULATE(max('Currency'[Fx Rate to USD]),TREATAS(values('Fact'[Date]),'Currency'[Date]),treatas(values('Fact'[Currency]),'Currency'[Currency]))),[A])
     

     


     

     
     
    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      2 years ago

       

      PY Sales USD = 
var a = summarize('Fact',[Currency],[Date])
var b = ADDCOLUMNS(a,"fx",CALCULATE(max('Currency'[Fx Rate to USD]),TREATAS({[Date]},'Currency'[Date]),treatas({[Currency]},'Currency'[Currency])))
var c = ADDCOLUMNS(b,"p",[PY Sales LC])
return sumx(c,[fx]*[p])

       

      • khaeshr46's avatar
        khaeshr46
        Frequent Visitor
        2 years ago

        Thanks alot!