Cummulative stocklevel backwards

[JKO75]

Hello,

I have

a table showing the current stock levels of today

a table showing the transaction done in the past, Ingoing and Outgoing where outgoing has a negative value and incoming a positive value. 

a date table

Stock
Product	Stock Level	Stock Value
A	100	10000
B	150	12000
C	75	6000
D	30	4000

Transactions
Date	Type	Product	Quantity	Value
26-7-2023	Out	A	-5	500
26-7-2023	Out	B	-10	800
25-7-2023	IN	C	25	2100
25-7-2023	IN	A	10	1000
25-7-2023	Out	D	-10	1350
24-7-2023	Out	D	-10	1350
24-7-2023	Out	A	-10	1000
24-7-2023	Out	B	-20	1600
24-7-2023	IN	B	50	4200
24-7-2023	IN	D	10	1250
24-7-2023	Out	C	-10	800

What I would like to have is the stock level and stock value for the each date. Any suggestions? 

Many thanks for your help

[Anonymous]

HI @JKO75,

You can add a variable to lookup the stock table based on the current product to get the initiation value.
Then you can write a cumulative calculation formula based on product and calculate with the initiation value of show the rolling results.

formula =
VAR currDate =
    MAX ( Transactions[Date] )
VAR currProduct =
    SELECTEDVALUE ( Transactions[Product] )
VAR init =
    LOOKUPVALUE ( Stock[Stock Value], Stock[Product], currProduct )
VAR rolling =
    CALCULATE (
        SUM ( Transactions[Value] ),
        FILTER ( ALLSELECTED ( Transactions ), [Date] <= currDate ),
        VALUES ( Transactions[Product] )
    )
RETURN
    init + rolling

Regards,

Xiaoxin Sheng

[JKO75]

Hello Sheng, Thank you. 

When using this I get the initial Stock value minus the total transaction done in the past. What I'm looking for is the Stocklevel for each day in the past. eg

Stocklevel Yesterday = Stocklevel - Transactions Yesterday and

Stocklevel day before Yesterday = Stocklevel Yesterday - Transaction day before yesterday. etc 

[Anonymous]

Hi @JKO75,

Sure, you can duplicate the rolling variable and remove the '=' operator to get the rolling result to previous. Then you can calculate with initialize stock with current rolling - previous rolling to get the daily stock:

formula =
VAR currDate =
    MAX ( Transactions[Date] )
VAR currProduct =
    SELECTEDVALUE ( Transactions[Product] )
VAR init =
    LOOKUPVALUE ( Stock[Stock Value], Stock[Product], currProduct )
VAR rolling =
    CALCULATE (
        SUM ( Transactions[Value] ),
        FILTER ( ALLSELECTED ( Transactions ), [Date] < currDate ),
        VALUES ( Transactions[Product] )
    )
VAR rollingtoDate =
    CALCULATE (
        SUM ( Transactions[Value] ),
        FILTER ( ALLSELECTED ( Transactions ), [Date] <= currDate ),
        VALUES ( Transactions[Product] )
    )
RETURN
    init + ( rollingtoDate - rolling )

Regards,

Xiaoxin Sheng