Forum Discussion

Petanek333's avatar
Petanek333
Helper III
4 years ago
Solved

Calculating stock based on weekly changes

Hi, I have two tables. One table of current stock to which I created an actual Year-Week column as it is showing the stock right at the moment of data import. and a table of stock changes in ...
Need Help
Show and Tell
  • PaulDBrown's avatar
    PaulDBrown
    4 years ago

    See if this works for you. (BTW, there was an inactive relationhip in the model you sent, which needs to made active)

    Since it's not clear whether you wish to see the value as the initial or the final stock of the week...

     

    Initial weekly Stock =
VAR _Current =
    CALCULATE (
        LASTNONBLANKVALUE ( 'WeekYear Start'[Year-Week], [Sum Stock] ),
        ALL ( 'WeekYear Start' )
    )
VAR _RunningStock =
    CALCULATE (
        [Sum Weekly Changes],
        FILTER (
            ALL ( 'WeekYear Start' ),
            'WeekYear Start'[Index] >= MAX ( 'WeekYear Start'[Index] )
        )
    )
RETURN
    _Current - _RunningStock

     

     

    Or...

     

    Final weekly Stock = 
VAR _Current = CALCULATE(LASTNONBLANKVALUE('WeekYear Start'[Year-Week], [Sum Stock]), ALL('WeekYear Start'))
VAR _RunningStock = CALCULATE([Sum Weekly Changes], FILTER(ALL('WeekYear Start'), 'WeekYear Start'[Index] >= MAX('WeekYear Start'[Index])))
VAR _PW = _RunningStock - [Sum Weekly Changes]
RETURN
_Current - _PW

     

     

    Or both even

    Btw, I the table for the current stock will only ever have one value per product, you can probably use a simple CALCULATE([Sum Stock], ALL(Weekyear Start)) instead of the CALCULATE(LASTNONBLANK... in the _Current variables


    I've attached the sample PBIX file

     

Petanek333's avatar
Petanek333
Helper III
4 years ago

OK, I think I made it, just created a backward Running Total measure like this

 

Just RT = 
var MinWeek = MIN('WeekYear Start'[Index])
return
    CALCULATE([Sum Weekly Changes],QuantityChangeTable[index to sort]>=MinWeek,ALL('WeekYear Start'))

 and then just summed it up with the sum of current stock, it works 🙂

However, I would like anyone to comment on it and say his or her opinion as I very much appreciate opinions of this community.

And finally, it is all done with the relationship between week table and current stock table. Is it the right to do or is different approach considered best practice?