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

7000x365 is 25 lakh - it is what it is.

 

Don't use 365 days.  Compute weekly or monthly stock levels.  Don't use measures.

 

I do similar computations over much larger date ranges and unit counts, and I do all these computations in my data source. The stock level of product A on last friday doesn't really change any more after the fact, so recomputing it in a Power BI measure is counterproductive, to say it nicely.

apvit's avatar
apvit
Frequent Visitor
5 years ago

Hello,

is there a chance to do a "virtual crossjoin" for Date and one Product in the measure?
My idea is to output stock level history for the currently selected product only.
In the hope to get rid of that large table Output=CROSSJOIN(Dates,Products)

tried this - but it is not working yet ("Output" is not allowed in this context...)

stock_current_product =

VAR selProduct=SELECTEDVALUE('Inventory'[product]) /*dropdown*/
VAR Output=CROSSJOIN('Dates',FILTER(VALUES(Inventory[product]),Inventory[product]=selProduct))
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