FX Rate calculation (works), but performance is poor. Help me DAX (link to sample file included)

[Jensk]

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. My file produces the desired result, but the calculation time is in the 30.000-45.000 ms range for the most heavy ones. I need help to alter my DAX in a way that reduces these.

In my real file I have a fact table of 227,515 rows and 22 columns that I crossjoin into a table of 3 million rows and 22 columns. I suspect this is the reason for the poor performance, but it is the only way I can make it work as desired. I am looking for any suggestions to improve the DAX and thus the performance of my report!

Here is a link to sample file with the desired result: https://drive.google.com/file/d/1t5JklmELBKq5urP0hRrlfY50AG6eiB2x/view?usp=sharing 

My Data:

DATE	ACCOUNT	CUR	AMOUNT	PERIODID	Index
01/01/2023	CashSEK	SEK	100.000	0	1
01/01/2023	CashUSD	USD	100	0	2
01/01/2023	CashGBP	GBP	1.000	0	3
01/01/2023	CashSEK	SEK	50.000	1	4
01/01/2023	CashUSD	USD	50	1	5
01/01/2023	CashGBP	GBP	500	1	6
01/01/2023	Receivables	DKK	1.000	1	7
01/01/2023	Receivables	EUR	10	1	8
01/01/2023	Receivables	USD	50	1	9
01/02/2023	Receivables	SEK	10.000	2	10
01/02/2023	Receivables	EUR	20	2	11
01/02/2023	Receivables	USD	160	2	12
01/03/2023	Receivables	GBP	25.000	3	13
01/03/2023	Receivables	EUR	30	3	14
01/03/2023	Receivables	USD	250	3	15

FX Rates:

My Desired result:

Example calculation for Receivables in March (Period ID 3):

Things worth noting:

• I need to present it with 13 columns: periods 0 through 12 (I only included 6 months in the example for limitation reasons)
• Period 0 represents the opening balance which is last years end balance. Periods 1 through 12 represent the months of the year Jan through Dec
• Transactions in period 0 is always 01-01-2023, but period 1 might also contain the same date, so date cannot be used as columns
• The fx rate date for period 0 is 31-12-2022 (because it is last years end balance carried forward)

The steps I made to get the result (see my file):

• I create a calculated table 'PERIOD' with values 0 through 12
• Then in order to calculate a value in fields that are blank I create: CrossJoin_Table = CROSSJOIN(FACTTABLE,PERIOD)
• In my CrossJoin_Table I add three calculated columns: CrossjoinBalance, CrossjoinBalAcc and USD_rate
• Finally I create my measure 'own_solution' that provides the desired result

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
    )

USD_rate = 
VAR FX_Date = 
SWITCH(
    TRUE(),
    CrossJoin_Table[PERIOD_ID_ALL] = 0, DATE(2022, 12, 31),
    CrossJoin_Table[PERIOD_ID_ALL] = 1, DATE(2023, 1, 1),
    CrossJoin_Table[PERIOD_ID_ALL] = 2, DATE(2023, 2, 1),
    CrossJoin_Table[PERIOD_ID_ALL] = 3, DATE(2023, 3, 1),
    CrossJoin_Table[PERIOD_ID_ALL] = 4, DATE(2023, 4, 1),
    CrossJoin_Table[PERIOD_ID_ALL] = 5, DATE(2023, 5, 1),
    CrossJoin_Table[PERIOD_ID_ALL] = 6, DATE(2023, 6, 1),
    CrossJoin_Table[PERIOD_ID_ALL] = 7, DATE(2023, 7, 1),
    CrossJoin_Table[PERIOD_ID_ALL] = 8, DATE(2023, 8, 1),
    CrossJoin_Table[PERIOD_ID_ALL] = 9, DATE(2023, 9, 1),
    CrossJoin_Table[PERIOD_ID_ALL] = 10, DATE(2023, 10, 1),
    CrossJoin_Table[PERIOD_ID_ALL] = 11, DATE(2023, 11, 1),
    CrossJoin_Table[PERIOD_ID_ALL] = 12, DATE(2023, 12, 1),
    BLANK()
)
RETURN
    LOOKUPVALUE(
        FXRATE[Rate],
        FXRATE[Currency], CrossJoin_Table[CUR],
        FXRATE[Date], FX_Date
    )

own_solution = 
SUMX(
    CrossJoin_Table,
    CrossJoin_Table[CrossjoinBalAcc] * CrossJoin_Table[USD_rate]
) 

I hope someone can come up with a smarter solution that yields the same results!

[Greg_Deckler]

@Jensk Have you tested these measures individually to see where the performance issue might lie? The calculations you are doing strike me as potentially similar to a recent CALCULATE challenge: Solved: Re: CALCULATE Challenge - Round 1 - Microsoft Fabric Community