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
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))
if (Cur_GR > cur_inv, cur_inv,Cur_GR )
and this logic goes on till greater than 2 years which as below
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))
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.