FX Rate calculation with a twist

I am building a balance sheet statement that I need to convert to USD. I have manipulated my data in a way that it always calculates the YTD amount depending on the column in the matrix. Now I need to convert these manipulated figures to USD.

My Data:

FACTTABLE (I added an Index column in the query):

FXRATE

My Raw Data looks like this:

To manipulate it I have made these changes:

• Created a table PERIOD:

PERIOD = 
SELECTCOLUMNS(
    GENERATESERIES(0, 12, 1),
    "PERIOD_ID_ALL", [Value]
)​

• Then in order to calculate a value in fields that are blank I create: CrossJoin_Table = CROSSJOIN(FACTTABLE,PERIOD)
• In  CrossJoin_Table I add these two calculated columns:

CrossjoinBalance = 
CALCULATE(
    SUM(FACTTABLE[AMOUNT]),
    FILTER(
        FACTTABLE,
        FACTTABLE[PERIODID] = CrossJoin_Table[PERIOD_ID_ALL]
            && FACTTABLE[Index] = CrossJoin_Table[Index]
    )
)

CrossjoinBalAcc = 
VAR CurrentPeriod = CrossJoin_Table[PERIOD_ID_ALL]
VAR Index = CrossJoin_Table[Index]
VAR FilteredTable =
    FILTER(
        CrossJoin_Table,
        CrossJoin_Table[PERIOD_ID_ALL] <= CurrentPeriod
            &&  Index = CrossJoin_Table[Index]
    )
RETURN
    CALCULATE( SUM(CrossJoin_Table[CrossjoinBalance]),
    FilteredTable
    )

This results in this layout of my data where I use CrossJoin_Table[ACCOUNT] as rows, CrossJoin_Table[PERIOD_ID_ALL] as columns and CrossJoin_Table[CrossjoinBalAcc] (sum):

For each column in the table I get the correct YTD amount for that period (ie. Receivables for period 2 = 1060+10180 = 11240) and if nothing was added during the month the previous month carries over.

My Data Model:

So far so good 😅.

Now I need to implement a currency calculation layer on top of this while maintaining this layout. 

The Raw data by currency looks like this:

For period 1 I would need to make this calculation:

• CashSEK = 100000*0,096174 (FX rate for SEK to USD for period 1 (January)) = 9617,40
• Receivables = 1000*0,145293 + 10*1,08313 + 50 = 206,12

I need to do this for the YTD amount, which means that the column in the matrix should filter the FX rates used for the entire amount. So the 100000 SEK has one USD value in period 1, but a new USD value in period 2 and so on...

My Desired outcome:

Note that since I only have FX rates for the first 3 months of 2024 I have selected the march rates for periods 4 through 12 (this does not need to be taken into account).

Any tips to how I calculate this is greatly appreciated.