Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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

DATEACCOUNTCURAMOUNTPERIODID
01-01-2024ReceivablesDKK10001
01-01-2024ReceivablesEUR101
01-01-2024ReceivablesUSD501
01-02-2024ReceivablesSEK100002
01-02-2024ReceivablesEUR202
01-02-2024ReceivablesUSD1602
01-03-2024ReceivablesGBP250003
01-03-2024ReceivablesEUR303
01-03-2024ReceivablesUSD2503
01-01-2024CashSEKSEK1000001
01-01-2024CashUSDUSD1001
01-01-2024CashGBPGBP10001

 

FXRATE

DateRateCurrency
01-01-20241,269036GBP
01-01-20240,096174SEK
01-01-20241,08313EUR
01-01-20240,145293DKK
01-01-20241,000000USD
01-02-20241,264798GBP
01-02-20240,096622SEK
01-02-20241,082626EUR
01-02-20240,145236DKK
01-02-20241,000000USD
01-03-20241,26183GBP
01-03-20240,093763SEK
01-03-20241,079144EUR
01-03-20240,144655DKK
01-03-20241,000000USD

 

My Raw Data looks like this:

Jensk_0-1713532568500.png

Jensk_1-1713532581089.png

 

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

 

Jensk_2-1713532878985.png

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:

Jensk_5-1713533785536.png

 

 

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:

 

Jensk_3-1713533190994.png

 

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:

Jensk_0-1713793556458.png

 

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
lbendlin
Super User
Super User

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

 

lbendlin_1-1713650462707.png

 

 

 

 

View solution in original post

4 REPLIES 4
lbendlin
Super User
Super User

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

 

lbendlin_1-1713650462707.png

 

 

 

 

hi @lbendlin 

 

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

Jensk_0-1713793556458.png

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

Jensk_0-1713789412166.png

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

Jensk_4-1713792140978.png

 

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

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.

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!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.