Forum Discussion
Dynamically Referencing above rows
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.
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
I cannot test on my laptop as I don't have access to it right now but you may try
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
)Thanks