cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

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])
1 ACCEPTED SOLUTION
Super User

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 8
Super User

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.

Frequent Visitor

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

Super User

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.

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

Super User

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

Frequent Visitor

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

Super User

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

Frequent Visitor

Thanks alot!

Announcements

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors