Forum Discussion

Ijimo's avatar
Ijimo
Frequent Visitor
10 years ago

Merge tables based upon Start and End dates(table without relation)

I have table Operation (Date , Amount) and table Exchenge_rate (start_date, rate, end_date).

The specific exchange rate has been for several days. In Exchenge_rate table this is one row.

I need merge this table (perhaps there is another way), and get value Currency_Amount = Value/rate.

Perhaps this is a simple task, but I still only starting their way in Power BI.

I will be very grateful for any help

Need Help

1 Reply

  • austinsense's avatar
    austinsense
    Impactful Individual
    10 years ago

    For starters, I would keep these tables separate and write a calculated column once the query is loaded.

     

    The calculated column on the Operation table might look like this ...

     

    Exchange Rate = CALCULATE( IFERROR(VALUES(Exchange_Rate[Rate]),BLANK()), FILTER(Exchange_Rate, EARLIER(Operation[Date])>=Exchange_Rate[Start_Date]&&EARLIER(Operation[Date])<=Exchange_Rate[End_Date]))