Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register 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. 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:
The steps I made to get the result (see my file):
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!
@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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
146 | |
72 | |
63 | |
52 | |
51 |
User | Count |
---|---|
208 | |
91 | |
62 | |
59 | |
56 |