cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

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):

 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.

1 ACCEPTED SOLUTION
Super User

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

4 REPLIES 4
Super User

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

Frequent Visitor

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:

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

Super User

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

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

Frequent Visitor

Hi @lbendlin

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

I have marked this as solved, thanks again!

Announcements

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors