FX Rate calculation with a twist

[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. Now I need to convert these manipulated figures to USD.

My Data:

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

DATE	ACCOUNT	CUR	AMOUNT	PERIODID
01-01-2024	Receivables	DKK	1000	1
01-01-2024	Receivables	EUR	10	1
01-01-2024	Receivables	USD	50	1
01-02-2024	Receivables	SEK	10000	2
01-02-2024	Receivables	EUR	20	2
01-02-2024	Receivables	USD	160	2
01-03-2024	Receivables	GBP	25000	3
01-03-2024	Receivables	EUR	30	3
01-03-2024	Receivables	USD	250	3
01-01-2024	CashSEK	SEK	100000	1
01-01-2024	CashUSD	USD	100	1
01-01-2024	CashGBP	GBP	1000	1

FXRATE

Date	Rate	Currency
01-01-2024	1,269036	GBP
01-01-2024	0,096174	SEK
01-01-2024	1,08313	EUR
01-01-2024	0,145293	DKK
01-01-2024	1,000000	USD
01-02-2024	1,264798	GBP
01-02-2024	0,096622	SEK
01-02-2024	1,082626	EUR
01-02-2024	0,145236	DKK
01-02-2024	1,000000	USD
01-03-2024	1,26183	GBP
01-03-2024	0,093763	SEK
01-03-2024	1,079144	EUR
01-03-2024	0,144655	DKK
01-03-2024	1,000000	USD

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.

[lbendlin · Accepted solution]

Wait what?  Your CashSEK value is not in DKK but in SEK?

[Jensk]

hi @lbendlin 

Thanks alot of the answer and file! I did indeed make an error with my data, sorry! Updated my desired outcome:

Your file does not quite get the right result as it uses the march-rates for all amounts:

But when I make a relation on Date between the Dates and FX tables in your file I gets the correct amounts 👌:

I have an issue when incorporating it into my own file. I need to have 13 period columns which is why I use the period_id instead of months as columns. Period 0 is the previous year carrying over. I cannot use month as columns because 01-01-2024 can be in both period 0 and period 1.

I tried incorporating your solution in my own file which I could not make work:

https://drive.google.com/file/d/1gNn7MqPnd25qSEpukG-c91e72w1SXPH1/view?usp=sharing 

How would you approach it if more data was added to your file and you had data in all 13 periods (0 through 12)?

[lbendlin]

I still don't understand why you would need a crossjoin table.  You can do the crossjoin in the visual.

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[Jensk]

Hi @lbendlin 

I have made a new post about the issue here (link to sample file included): https://community.fabric.microsoft.com/t5/Desktop/FX-Rate-calculation-works-but-performance-is-poor-...

If you have time to help me I would greatly appreciate it!

I have marked this as solved, thanks again!