Join tables with missing values? or fill the gaps?

[diegoadum]

Hi folks, I think I'm overcomplicating here so I need your wise support!! 

I have 2 tables, one is transactions and the other is currency rates. On the currency rates, I have the rates for a particular period maybe I have only 2 records because the first one belongs to October 2020 and the second one belongs to December 2020. Something like this:

C1RTTYC1CRDCC1CRCDC1CRRStd Cost EREffectiveDateYearMonth

CR	USD	ARA	0.0133333	75.00	10/1/2020	2020	12
CR	USD	ARA	0.0093067	107.45	12/1/2020	2020	12

Sorry for the field names!!. Anyway, as you can see we have 2 records. Now on the other side, we have the transaction table that has many records for each day. So the goal is to use the Rate from the Currency Rates table to get the conversion for each transaction in the transaction table. I've tried different kinds of merge models but every way is pushing me to the fact that I need to create many records in the currency rates tables as different days exist in the transaction table. 

I'm wondering if there is a way to join the tables with some kind of >= operator that connects those records without a direct link.

BTW, all transactions before December 1st should use 75 USD per ARA, and transactions on December 1st and beyond must use 107.45 USD per ARA.

Thanks in advance for the help!!!

[v-henryk-mstf]

Hi @diegoadum ,

Do you mean you want to implement the requirements in power query? Can you end the meaning of the specific fields and the expected result you expect, I will answer you as soon as possible.

Best Regards,
Henry

[diegoadum]

Hi Henry, the values will be part of the formula within the Power Query. The whole exercise in focusing on inventory revaluation which is a common problem in countries with high inflation scenarios. So far, I solved the problem by adding as many records as months exist in the year due to my union needs Year as well as Month between the two tables. The main goal is to create a calculated column with the revaluation amount. Thanks!

[AlexisOlson]

Currency conversion can be a big headache. I recommend checking out this article for some best-practice patterns for it: https://www.daxpatterns.com/currency-conversion/

In any case, you'll almost certainly want a Date table to work with in your report.

[diegoadum]

Thank Alexis for your help, but in this case, I'm forced to apply the solution as M language inside Power Query, (I should say that before!! 😉 due to base on this info I need to still work with some other fields.