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

     

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

In your situation it is easier (and faster) to use TREATAS to project the filter across the sales and FX tables.

 

If you like more assistance - please share sample data.

  • 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])