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

a date table

Stock  
ProductStock LevelStock Value
A10010000
B15012000
C756000
D304000

 

Transactions    
DateTypeProductQuantityValue
26-7-2023OutA-5500
26-7-2023OutB-10800
25-7-2023INC252100
25-7-2023INA101000
25-7-2023OutD-101350
24-7-2023OutD-101350
24-7-2023OutA-101000
24-7-2023OutB-201600
24-7-2023INB504200
24-7-2023IND101250
24-7-2023OutC-10800

 

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

Many thanks for your help

Need Help

3 Replies

  • 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