Forum Discussion

apvit's avatar
apvit
Frequent Visitor
5 years ago
Solved

Calculating stock level

Hi, I have recently started using PowerBI.  Please help me with this DAX calculation:   The available quantity of a product is logged in a table every time it's changing. stock level product da...
Need Help
  • lbendlin's avatar
    lbendlin
    5 years ago

    You're moving in the right direction with the date table. Do not connect that table to your data model (or make all connections inactive, or use CROSSFILTER(,,none)  for a cartesian product).

     

    Dates=CALENDAR("2020-01-01","2020-02-25")

     

    What you need to do next is for each date from that table and each product to calculate a measure of the latest inventory.  If you want to arrive at the desired output that you indicate then I would recommend creating another lookup table

     

    Products = VALUES(Inventory[product])
     
    and then to do the crossjoin between the two
     
    Output = CROSSJOIN(Dates,Products)
     
    This table will feed your visual.
     
    The last thing to do is add the measure for the inventory

     

    stock =
    VAR p =
        MAX ( Output[product] )
    VAR d =
        MAX ( Output[Date] )
    VAR a =
        CALCULATE (
            MAX ( Inventory[date] ),
            Inventory[date] <= d,
            Inventory[product] = p
        )
    VAR s =
        IF (
            ISBLANK ( a ),
            0,
            CALCULATE (
                MAX ( Inventory[quantity] ),
                Inventory[date] = a,
                Inventory[product] = p
            )
        )
    RETURN
        s

     
    Below is the result, filtered down to only the dates where something is happening
     

     

lbendlin's avatar
lbendlin
Super User
5 years ago

You're moving in the right direction with the date table. Do not connect that table to your data model (or make all connections inactive, or use CROSSFILTER(,,none)  for a cartesian product).

 

Dates=CALENDAR("2020-01-01","2020-02-25")

 

What you need to do next is for each date from that table and each product to calculate a measure of the latest inventory.  If you want to arrive at the desired output that you indicate then I would recommend creating another lookup table

 

Products = VALUES(Inventory[product])
 
and then to do the crossjoin between the two
 
Output = CROSSJOIN(Dates,Products)
 
This table will feed your visual.
 
The last thing to do is add the measure for the inventory

 

stock =
VAR p =
    MAX ( Output[product] )
VAR d =
    MAX ( Output[Date] )
VAR a =
    CALCULATE (
        MAX ( Inventory[date] ),
        Inventory[date] <= d,
        Inventory[product] = p
    )
VAR s =
    IF (
        ISBLANK ( a ),
        0,
        CALCULATE (
            MAX ( Inventory[quantity] ),
            Inventory[date] = a,
            Inventory[product] = p
        )
    )
RETURN
    s

 
Below is the result, filtered down to only the dates where something is happening
 

 

  • apvit's avatar
    apvit
    Frequent Visitor
    5 years ago

    Thank you very much!!! For my test data it works perfectly. 
    But 7.000 products and a date range of 365 days make calculations very very slow. 

    Do you have any ideas how to optimize it?

  • Prov's avatar
    Prov
    Regular Visitor
    4 years ago

    Hey, great answer on the topic.
    I'm actually trying to achieve the same result, but I'm struggling in implementing your solution as my stock is always equal to 1.
    My 'Stock Level' is your Inventory, and I followed your logic for Products, Dates and Output, but I keep getting all 1 on the stock column.

    Think you could help me out? This is a bit beyond my expertise I admit.

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