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

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