Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to 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])
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.
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 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])
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 |
I don't see a difference to the other columns.
Thanks alot!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
22 | |
21 | |
20 | |
14 | |
11 |
User | Count |
---|---|
43 | |
34 | |
25 | |
23 | |
23 |