Currency Conversion Issue - Add Rate Where Null and Calculate
I am trying to convert Unit Price into USD using the RATE_MULT field. My conversion table does not have a value for USD prior to April 2012. Where this value is null in the RATE_MULT column, I have replaced it with Num#(1) using Calculated Dimension. The problem comes about then when I try to calculate an expression (USD Amount) off of the calculated dimension. The calculation does not recognize the new (1), and only a null value is returned in USD Amount.
Perhaps the synthetic key is the issue. I had to join on MonthYear as well as Currency Paid due to the dates not being exact matches.
I have attached the file here. Thanks for your assistance.