Constant Currency dynamic calculation help needed

[khaeshr46]

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]

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]

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

Date	Currency	Fx Rate to USD
07/31/13	USD	1.00000
07/31/13	CAD	1.02870
08/31/13	EUR	1.32350
08/31/13	USD	1.00000
08/31/13	CAD	1.05530
09/30/13	CAD	1.02850
09/30/13	EUR	1.35050
09/30/13	GBP	1.61530
09/30/13	USD	1.00000

Fact Table

Date	LC Amount	Currency	ProductID
07/31/12	96,444,348.00	USD	113
07/31/12	272,570,350.80	CAD	122
07/31/12	41,111,070.00	USD	129
08/31/12	153,148,122.70	CAD	122
08/31/12	54,166,612.50	USD	129
09/30/12	352,368,933.70	CAD	122
09/30/12	290,977,531.20	USD	113
09/30/12	41,388,847.50	USD	129
07/31/13	41,772,180.45	USD	129
07/31/13	466,478,260.70	CAD	122
08/31/13	65,741,175.92	EUR	157
08/31/13	66,666,600.00	USD	113
08/31/13	25,972,196.25	USD	129
08/31/13	378,041,253.10	CAD	122
09/30/13	14,922,207.30	USD	129
09/30/13	281,268,319.20	CAD	122
09/30/13	58,975,793.59	EUR	157
09/30/13	94,719,152.48	GBP	176
09/30/13	66,666,600.00	USD	113

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]

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]

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 · Accepted solution]

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]

Hi,

Im still struggling to create a new measure that uses the the MAX date's rate depending what the user has selected in the slicer.

Below is my expected output.

Date	Currency	Sales LC	Sales USD	Sales USD Correct Total	Sales USD with Latest Rates
7/31/2012 0:00	CAD	$272,570,350.80
7/31/2012 0:00	USD	$137,555,418.00
8/31/2012 0:00	CAD	$153,148,122.70
8/31/2012 0:00	USD	$54,166,612.50
9/30/2012 0:00	CAD	$352,368,933.70
9/30/2012 0:00	USD	$332,366,378.70
7/31/2013 0:00	CAD	$466,478,260.70	$479,866,186.78	$479,866,186.78	$479,772,891.13
7/31/2013 0:00	USD	$41,772,180.45	$41,772,180.45	$41,772,180.45	$41,772,180.45
8/31/2013 0:00	CAD	$378,041,253.10	$398,946,934.40	$398,946,934.40	$388,815,428.81
8/31/2013 0:00	EUR	$65,741,175.92	$87,008,446.33	$87,008,446.33	$88,783,458.08
8/31/2013 0:00	USD	$92,638,796.25	$92,638,796.25	$92,638,796.25	$92,638,796.25
9/30/2013 0:00	CAD	$281,268,319.20	$289,284,466.30	$289,284,466.30	$289,284,466.30
9/30/2013 0:00	EUR	$58,975,793.59	$79,646,809.24	$79,646,809.24	$79,646,809.24
9/30/2013 0:00	GBP	$94,719,152.48	$152,999,847.00	$152,999,847.00	$152,999,847.00
9/30/2013 0:00	USD	$81,588,807.30	$81,588,807.30	$81,588,807.30	$81,588,807.30

[lbendlin]

I don't see a difference to the other columns.

[khaeshr46]

Thanks alot!