Forum Discussion
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. ...
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,
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
Super User
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])
Thanks alot!
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.