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

So I thought I solved it, but I realized one mistake that I cannot fix.

First table is current stock, second table are stock movements and the measure on the top of the picture is used in that table and it works fine. 

Thing is I realized that the last week's value in the top table already contains the stock movement. So the running total (running total is a separate measure) should calculate all the selected weeks except for the last week.

I tried modifying the measure like this but it results in the last table 🙂

 

Can you please help me? danextian PaulDBrown  Sample file is attached below.

Sample report 

PaulDBrown's avatar
PaulDBrown
Community Champion
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

    Paul, I really appreciate your help. Thank you very much!