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
Hello all,
I encounter the error by showing not enough memory when using earlier funciton.
My purpose is to get accumulated qty based on same date and same sku. The example below:
Date_Created | Material | Quantity | Index | Accumulated_Qty |
25-Oct | A | 100 | 1 | 100 |
25-Oct | A | 200 | 2 | 300 |
25-Oct | A | 300 | 3 | 600 |
25-Oct | B | 400 | 4 | 400 |
25-Oct | B | 500 | 5 | 900 |
25-Oct | B | 600 | 6 | 1500 |
The DAX I used:
Accumulated_Qty =
I also tried to use var but still failed:
Qty_Accum =
VAR CurrentIndex = Basic_1[Index]
VAR CurrentMaterial = Basic_1[Material]
VAR CurrentDate = Basic_1[Date created]
RETURN
SUMX(
FILTER(
Basic_1,
Basic_1[Index] <= CurrentIndex &&
Basic_1[Material] = CurrentMaterial &&
Basic_1[Date created] = CurrentDate
),
Basic_1[Quantity]
)
Is there any other way to improve that? Thanks for help!
Solved! Go to Solution.
Hi @nbufff
Can try the window function, It may have better performance.
Accumulated_Qty =
SUMX(
WINDOW(1,ABS,0,REL,ORDERBY('Basic_1'[Index],ASC,'Basic_1'[Quantity]),PARTITIONBY(Basic_1[Date_Created],'Basic_1'[Material])),
'Basic_1'[Quantity]
)
Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !
Thank you~
Great solution. Thanks to let me know the Window fuction.
Hi @nbufff
Can try the window function, It may have better performance.
Accumulated_Qty =
SUMX(
WINDOW(1,ABS,0,REL,ORDERBY('Basic_1'[Index],ASC,'Basic_1'[Quantity]),PARTITIONBY(Basic_1[Date_Created],'Basic_1'[Material])),
'Basic_1'[Quantity]
)
Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !
Thank you~
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 |
---|---|
26 | |
21 | |
11 | |
11 | |
11 |
User | Count |
---|---|
60 | |
31 | |
22 | |
19 | |
19 |