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

Convert your selectedvalue into a single row/single column table and then do the cross join against that.

 

Computationally there will be no real benefit though.