Historic Blended Fx rate - column self referencing help

Hi all,

First post, happy new year, and apologies if I miss critical info, appreciate anybody that tries to help with the mess below 😅.

Situation: Accounting for Leases in non-functional currency.

I'm trying to create a powerBI model, that takes accounting journals that are presented in a contract's currency, and will convert these to the appropriate functional currency value for the month in question. I created a model last year, that broadly did what I needed it to, but had trouble with my depreciation charge because I needed to calculate a blended FX rate in a calcualted colum that would allow the posted functional NBV to be depreciated to zero. This meant I was referencing the column I was trying to write a formula in to try and get the previous row's values for it - and PBI didn't like a calc'd column that was self referencing. I ended up doing a work around that was materially correct, but not right, which I am trying to remedy now.

Looking to understand if what I am looking to do is possible, and the best way to do this - I think probably in M code hence posting in this formum, or either a calc'd column or measure.

Ultimately, I will use the functional currency amounts in an aggregation to create subledgers by lease and identify what journals need to be posted each month. Would like to be able to publish this so it is accessible to people from the different markets and functions which I why I'm trying it in PBI, and I'm not familiar with other tools (SQL/Python etc) unfortunately - so if PBI isn't the one, I may have to accept the ideal solution isn't possible.

Assume I have two tables:

Table1: Data_Journals (please ignore the rightmost two column for now - these are what I want to add and are referred to below, but included here to save space/repetition)

and Table2: Lookup_FX_Rates_table

In my PBI, there is a calendar table that creates the relationship between the event date from table1 and that month's closing fx rate in table2 using an end of month column in the calendar table.

In excel on a small scale with this data I can easily add the two columns I need to Data_Journals to calc the functional currency amounts needed (see the rightmost two columns in Table1 above). Where my formulas in those two columns are essentially (being the formulas in the top row which is dragged down):

• Relevant FX Rate=IF(I2="Closing",SUMIFS(Lookup_FX_Rates_table!$E$2:$E$13,Lookup_FX_Rates_table!$C$2:$C$13,Data_Journals!F2,Lookup_FX_Rates_table!$D$2:$D$13,EOMONTH(Data_Journals!C2,0)),SUM($L$1:L1)/SUM($J$1:J1))
• Functional Currency Amount=J2*K2

I am looking for the best solution for how to get the same result in PBI. It would need to be scalable - as I have a large number of journals, and it would be a part of that column, as the extract I have showed is just for ROU, I will have other lines with different categories that I am comfortable pulling the correct fx rate for to use in the calc within a calc'd column.

Incase the solution is in M, then essentially the column "Rate-to-use" defines which I need, either the closing for the currency pair, the average where both closing and average are specified already in the Lookup_FX_Rates_table - or the calculated blended historic closing which would be unique for each non functional currency lease.
Previously for closing and average, I had a separate table for each, Lookup_FX_Rates_table_Average & Lookup_FX_Rates_table_Closing, but I might try making just one table now if it is easier/cleaner.

Very sorry if this is unclear, if more info is required, I will try to share as best I can. Thanks in advance all.

Edit: Link to excel file:
PowerQuery Forum Help file - Blended historic FX rate.xlsx