Material QTYRateClosing Stock Date1500000.2150000 11-Apr-2300.2120000 12-Apr-2300.296000 13-Apr-2300.276800 14-Apr-2300.261440 15-Apr-2300.249152 16-Apr-2300.239322 17-Apr-2300.231457 18-Apr-2300.225166 19-Apr-231000000.15106391 20-Apr-2300.1590432 21-Apr-2300.1576867 22-Apr-2300.1565337 23-Apr-2300.1555537 24-Apr-23How 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).

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 )

Forum Discussion

NaveenMD

Helper I

3 years ago

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	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

3 years ago

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
    )

Community 590 - Semi-Recursive.pbix41 KB

NaveenMD

3 years ago

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

5 Replies

tamerj1

Community Champion

3 years ago

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
    )

Community 590 - Semi-Recursive.pbix41 KB

NaveenMD
Helper I
3 years ago
Thank you for the solution this seems to work.

NaveenMD

Helper I

3 years ago

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
Community Champion
3 years ago
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
)

Forum Discussion

Dynamically Referencing above rows

5 Replies

Related content

Dynamically referencing above rows in the same column

Circular Dependency Issue Referencing Line Above

Dynamic Filter

Dynamically Reference Calculated Row Above

referencing a calculated measure value, a row above