Daily Currency Exchage Rate

Comments

• VidasMatelis

  September 2016

  Hi,
  
  In the Daily worksheet if you go to the right far enough (column AI5), you will see Pivot Table that is used to build this report. There is a filter for CurrencyID = 2 - that is probably USD currency and CurrencyID = 1 probably your CAD currency).
  If you have Excel 2010 or Excel 2013/2016 with full PowerPivot support (you see PowerPivot menu) then you can easily edit measure "Exch Rate to Primary" - I believe it is used just in this report.
  So you could change formula from:

  =IF(NOT(HasOneValue(ReportCurrency[ReportCurrency])), Blank()
  	, IF(VALUES(ReportCurrency[ReportCurrency]) = CALCULATE(VALUES(ReportCurrency[ReportCurrency]), ReportCurrency[CurrencyID]=1)
  		, Blank()
  		, CALCULATE(AVERAGE(CurrencyConv[ExchRate])
  			, FILTER(ALL(CurrencyConv[CurrencyTo]), CurrencyConv[CurrencyTo] =VALUES(ReportCurrency[ReportCurrency]))
  			, FILTER(ALL(CurrencyConv[CurrencyFrom]), CurrencyConv[CurrencyFrom] = CALCULATE(VALUES(ReportCurrency[ReportCurrency]), ReportCurrency[CurrencyID]=1, ALL(ReportCurrency)))
  		  )
  	  )
  )

  to formula

  =IF(NOT(HasOneValue(ReportCurrency[ReportCurrency])), Blank()
  	, IF(VALUES(ReportCurrency[ReportCurrency]) = CALCULATE(VALUES(ReportCurrency[ReportCurrency]), ReportCurrency[CurrencyID]=2)
  		, Blank()
  		, CALCULATE(AVERAGE(CurrencyConv[ExchRate])
  			, FILTER(ALL(CurrencyConv[CurrencyTo]), CurrencyConv[CurrencyTo] =VALUES(ReportCurrency[ReportCurrency]))
  			, FILTER(ALL(CurrencyConv[CurrencyFrom]), CurrencyConv[CurrencyFrom] = CALCULATE(VALUES(ReportCurrency[ReportCurrency]), ReportCurrency[CurrencyID]=2, ALL(ReportCurrency)))
  		  )
  	  )
  )

  And then in the Pivot Table I talked about above you would change filter condition to CurrencyID = 1.
  
  What version of Excel do you have and do you have PowerPivot full support?
  
  
  
  
• patr0kxxx

  September 2016

  Excel 2016 with full powerpivot support
• VidasMatelis

  September 2016

  In Excel 2016:
  PowerPIvot->Measures->Manage Measures-> Select "Exch Rate Impact"-> Edit. In the formula bar enter formula from above.
  
  Refresh and you are done.
• patr0kxxx

  September 2016

  Thanks for the quick followup Vidas,
  
  I modified the formula as instructed but then the graph simply disappeared along with the value in column AI5. Do i need to modify psConfig.txt or similar to get USD/CAD instead of CAD/USD ?
• VidasMatelis

  September 2016

  YOu should already have CAD->USD and USD-CAD rates - you could double check in the file.
  You should also change filter for that report from "CurrencyID : 2" to "CurrencyID: 1"
• patr0kxxx

  September 2016

  changing the filter to 1 did the trick, thanks a lot for the great support