Convert Fx rates of current month to budget fx rate

[pi8080]

Hi all,

I have a file named "currency fx rate" which shows the monthly fx rate for several currencies compared to USD ( Group currency).

I have other two files containing data for Actuals amounts and Budget amounts.

Assuming the fiscal year is from September to August, the budget amounts use already Sept fx rates (period 1) so they dont need conversion.

However Actuals amount ( aside from Sept) needs conversion to budget fx rate based on the month/period they are in.

In the screenshot below there are several monthly currencies value to USD with different amount each month.

what I would like to do is converting actuals of each period in the actual file using period 1 fx rate. 

So i need a formula that say if the euro actuals or gbp actuals are in period 2, convert it to period 1 fx rate. The same is true for the other periods.

having several currencies and several periods make it a bit complex for me to come up with a solution.

In excel i would probably use an index match using periods and currencies.

Thanks

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...