Missing FX Rates

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,

Expect User Prior Month Exchange Rate.xlsx

Use Prior Month Exchange Rate.yxmd

Workflow

Datasets

Data Investigation

Accepted answers

danilang

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

Use Prior Month Exchange Rate v2.yxmd

All comments

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

Use Prior Month Exchange Rate.yxmd

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703