Forum Discussion
Solved
Dynamically Referencing above rows
Material QTY Rate Closing Stock Date 150000 0.2 150000 11-Apr-23 0 0.2 120000 12-Apr-23 0 0.2 96000 13-Apr-23 0 0.2 76800 14-Apr-23 0 0.2 61440 ...
Hi NaveenMD
Please refer to attached sample file with the proposed solutionClosing Stock = VAR MinDate = MINX ( FILTER ( 'Table', 'Table'[Material QTY] > 0 ), 'Table'[Date] ) VAR T1 = FILTER ( 'Table', 'Table'[Date] <= EARLIER ( 'Table'[Date] ) ) RETURN SUMX ( T1, VAR Qty = 'Table'[Material QTY] VAR T2 = FILTER ( T1, 'Table'[Date] >= EARLIER ( 'Table'[Date] ) ) VAR Rate = PRODUCTX ( T2, IF ( 'Table'[Date] = MinDate, 1, (1 - 'Table'[Rate] ) ) ) RETURN Qty * Rate )I was able to identify one caviate with this formula
Material QTYRateDateClosing Stock
0 0.2 11 April 2023 0 0 0.2 12 April 2023 0 0 0.2 13 April 2023 0 0 0.2 14 April 2023 0 0 0.2 15 April 2023 0 0 0.2 16 April 2023 0 150000 0.2 17 April 2023 120000 0 0.2 18 April 2023 96000 0 0.2 19 April 2023 76800 100000 0.15 20 April 2023 150280 0 0.15 21 April 2023 127738 0 0.15 22 April 2023 108577 0 0.15 23 April 2023 92291 0 0.15 24 April 2023 78447 Lets say on 17th April if we add 150000 then it should be reflected as 150000 not 120000. 120000 should be the value for next day.
Excel Formula :=((1-0.2)*E2)+A3
Material QTY Rate Date Closing Stock Closing Stock 2 0 0.2 11-Apr-23 0 0 0 0.2 12-Apr-23 0 0 0 0.2 13-Apr-23 0 0 0 0.2 14-Apr-23 0 0 0 0.2 15-Apr-23 0 0 0 0.2 16-Apr-23 0 0 150000 0.2 17-Apr-23 120000 150000 0 0.2 18-Apr-23 96000 120000 0 0.2 19-Apr-23 76800 96000 100000 0.15 20-Apr-23 150280 176800 0 0.15 21-Apr-23 127738 141440 0 0.15 22-Apr-23 108577 113152 0 0.15 23-Apr-23 92291 90521.6 0 0.15 24-Apr-23 78447 72417.28 Please help
Hi NaveenMD
Please refer to attached sample file with the proposed solution
Closing Stock =
VAR MinDate = MINX ( FILTER ( 'Table', 'Table'[Material QTY] > 0 ), 'Table'[Date] )
VAR T1 = FILTER ( 'Table', 'Table'[Date] <= EARLIER ( 'Table'[Date] ) )
RETURN
SUMX (
T1,
VAR Qty = 'Table'[Material QTY]
VAR T2 = FILTER ( T1, 'Table'[Date] >= EARLIER ( 'Table'[Date] ) )
VAR Rate =
PRODUCTX (
T2,
IF ( 'Table'[Date] = MinDate, 1, (1 - 'Table'[Rate] ) )
)
RETURN
Qty * Rate
)
Thank you for the solution this seems to work.