Forum Discussion

JKO75's avatar
JKO75
Frequent Visitor
3 years ago

Cummulative stocklevel backwards

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 v...
Need Help
JKO75's avatar
JKO75
Frequent Visitor
3 years ago

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's avatar
Anonymous
Not applicable
3 years ago

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