Memory Issue for calculated measure

anantjoshi2409 — Mon, 23 Jan 2023 15:19:11 GMT

Hello Everyone,

We are working on Power BI Report, where we need to calculation which are interdependent.

below is the logic to GR Value for 01-30 Days

GR Value 01-30 Days =

Var Cur_GR= CALCULATE([GR Value],Aging[Aging Period] = "01-30 Days")

var cur_inv = CALCULATE( CALCULATE(sum(Query1[Ending Inventory Value]),Aging[Aging Period] = "01-30 Days"),ALL(Aging))

Return

if (Cur_GR > cur_inv, cur_inv,Cur_GR )

and this logic goes on till greater than 2 years which as below

GR Value 1-2 Years =

Var Cur_GR= CALCULATE([GR Value 01-30 Days],ALL(Aging))

Var Pre_GR= CALCULATE(CALCULATE([GR Value 01-30 Days])+CALCULATE([GR Value 31-60 Days])+CALCULATE([GR Value 61-90 Days])+CALCULATE([GR Value 91-120 Days])+CALCULATE([GR Value 121-150 Days])+CALCULATE([GR Value 151-180 Days])+CALCULATE([GR Value 6-12 Months]),ALL(Aging))

Var Tot_GR=CALCULATE(CALCULATE([GR Value 01-30 Days])+CALCULATE([GR Value 31-60 Days])+CALCULATE([GR Value 61-90 Days])+CALCULATE([GR Value 91-120 Days])+CALCULATE([GR Value 121-150 Days])+CALCULATE([GR Value 151-180 Days])+CALCULATE([GR Value 6-12 Months]),ALL(Aging))+CALCULATE([GR Value],Aging[Aging Period] = "1-2 Years")

Var Prd_GR=CALCULATE([GR Value],Aging[Aging Period] = "1-2 Years")

var cur_inv = CALCULATE( CALCULATE(sum(Query1[Ending Inventory Value]),Aging[Aging Period] = "01-30 Days"),ALL(Aging))

Return

IF(Cur_GR>cur_inv, 0,

IF(Tot_GR>cur_inv, cur_inv-Pre_GR,Prd_GR))

This calculation of GR Value for the particular aging period requires to consider values from all previous aging periods so we are getting error with Memory issue, and we have 9 Aging Period ranging from 01-30 days till >2 Years.

Kindly suggest any optimization we could make it work.

topic Memory Issue for calculated measure in Developer

Memory Issue for calculated measure