Missing FX Rates

[Tessa_Gable]

Hello,

I have another FX question...All my opportunities in foreign currency are not making it into my data source.  What is the best way to create a formula to say if the current month rates haven't been loaded use the prior month.  I have attached a sample workflow and the excel sheet highlighted yellow what I expect to see.  I'm probably making it more complicated than it is.

Thanks,

[demand_james]

HI Tessa -

See if this works for you.   I put a dynamic row formula tool on the end to fill in any null values with the value above.   Note the sort before hand.

You could also generate rows on the "front" side of the workflow to add dates to match up to the max date of the currency start.

James

[Tessa_Gable]

This might work but I have close to a million rows in the result of the workflow. I haven't had to use the create row tool but that would probably be best.

Thank you for your quick response.

[danilang · Accepted solution]

Hi @Tessa_Gable 

Since you've got a very large Opportunity dataset and a relatively small FX one, I would recommend using a process in the front, similar to what @demand_james mentioned generating the missing FX rates.

Get a list of the unique FXDateStart from your Opportunities and join this to the FX table on Date.  Append the missing dates(L output) to the matched ones.  Copy the dates from the FXDateStart field to the StartDate and union these with the matched records.  The output of this is a relatively small table(~200 currencies X Number of months) with all the required dates for all currencies.

When you join this to your main table and union the results the missing values will be filled in  

Dan