topic Error showed not enough memory by using Earlier in DAX Commands and Tips

Error showed not enough memory by using Earlier

nbufff — Fri, 25 Oct 2024 09:01:54 GMT

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 =

VAR SumQuantity =

CALCULATE(

SUM(Basic_1[Quantity]),

FILTER(

Basic_1,

Basic_1[Index]<= EARLIER(Basic_1[Index]) &&

Basic_1[Material] = EARLIER(Basic_1[Material]) &&

Basic_1[Date created] = EARLIER(Basic_1[Date created])

)

RETURN

IF(ISBLANK(SumQuantity), 0, SumQuantity)

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!

Re: Error showed not enough memory by using Earlier

xifeng_L — Fri, 25 Oct 2024 10:02:30 GMT

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~

Re: Error showed not enough memory by using Earlier

nbufff — Fri, 25 Oct 2024 10:47:12 GMT

Great solution. Thanks to let me know the Window fuction.