March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet 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
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:
PERIOD =
SELECTCOLUMNS(
GENERATESERIES(0, 12, 1),
"PERIOD_ID_ALL", [Value]
)
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:
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.
Solved! Go to Solution.
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)?
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!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
116 | |
88 | |
78 | |
65 | |
59 |
User | Count |
---|---|
140 | |
122 | |
105 | |
94 | |
90 |