Forum Discussion

Prov's avatar
Prov
Regular Visitor
4 years ago
Solved

Calculating Stock Level

Hi,
I'm pretty new to Power BI.
I'm trying to calculate the stock level of a product at any given date.
I've generated tables for when an item is put in stock and for any stock variations, appended them in power query and built a running total  (Variation and Stock Levels in my example file).


Then, to have the product levels at any given date, I built Dates and Products tables and Crossjoined them into an Output table.
What I'm failing to do is to feed correctly this table with inventory levels of each product for each date.

I've been trying to follow this post to reach a solution:
https://community.powerbi.com/t5/Desktop/Calculating-stock-level/m-p/1693852/highlight/true#M673067

Here is my sample file:
https://drive.google.com/file/d/1UWVSCX03KW7LXU8YaRvgYVreMHhSLD_x/view?usp=sharing

Hope anyone can help me, I'm pretty confused right now on why I'm not being able to get this right.

Need Help
  • HotChilli's avatar
    HotChilli
    4 years ago

    stock in the output table was written like a measure, not a calculated column.

    I've made some changes which look ok but please test at your side:

    stock = 
VAR _product = Output[Product]
VAR _date = Output[Date]
VAR _stLevDate = CALCULATE (
        MAX ( 'Stock Levels'[Date] ),
        'Stock Levels'[Date] <= _date,
        'Stock Levels'[Product] = _product
    )
VAR _stock =
    IF (
        ISBLANK ( _stLevDate ),
        0,
        CALCULATE (
            MAX ( 'Stock Levels'[Stock]),
            'Stock Levels'[Date] = _stLevDate,
            'Stock Levels'[Product] = _product
        )
    )
RETURN
    _stock

2 Replies

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    4 years ago

    stock in the output table was written like a measure, not a calculated column.

    I've made some changes which look ok but please test at your side:

    stock = 
VAR _product = Output[Product]
VAR _date = Output[Date]
VAR _stLevDate = CALCULATE (
        MAX ( 'Stock Levels'[Date] ),
        'Stock Levels'[Date] <= _date,
        'Stock Levels'[Product] = _product
    )
VAR _stock =
    IF (
        ISBLANK ( _stLevDate ),
        0,
        CALCULATE (
            MAX ( 'Stock Levels'[Stock]),
            'Stock Levels'[Date] = _stLevDate,
            'Stock Levels'[Product] = _product
        )
    )
RETURN
    _stock
    • Prov's avatar
      Prov
      Regular Visitor
      4 years ago

      This works! Thank you very much, now that I see the solution I understand my mistake.
      Still need to learn a lot about DAX. Thanks!