Dynamically Referencing above rows

[NaveenMD]

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		15-Apr-23
0	0.2	49152		16-Apr-23
0	0.2	39322		17-Apr-23
0	0.2	31457		18-Apr-23
0	0.2	25166		19-Apr-23
100000	0.15	106391		20-Apr-23
0	0.15	90432		21-Apr-23
0	0.15	76867		22-Apr-23
0	0.15	65337		23-Apr-23
0	0.15	55537		24-Apr-23

How do we calculate the closing stock based on the values in the column Material Qty 
The formula used in excel is given as =(C2+A3)*(1-B3).

[tamerj1 · Accepted solution]

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
    )

[tamerj1 · Accepted solution]

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
    )

[NaveenMD · Accepted solution]

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

[tamerj1]

@NaveenMD 

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
)

[NaveenMD]

Thanks

[NaveenMD]

Thank you for the solution this seems to work.