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. 

 

 

 

I need to convert the PY Sales LC x 9/30/15 rates for my PY Sales USD(CC)

 

The mesaure below is giving me the correct CC amount Im looking for in the current slicer selection. How can I modify this to apply for Prior Year Sales LC

 

Sales USD (CC) =
var ccDate =
Constant Currency Date = CALCULATE(
        MAX('FX Rate'[Date]),
        ALLSELECTED('Calendar_Lookup'[Date])
)
VAR vSalesWithConstantRate =
SUMMARIZE(
    Sales,
    'Currency'[Currency],
    "_LC Amount",
    [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
    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])

     

8 Replies

  • lbendlin's avatar
    lbendlin
    Super 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
        Super 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.