Calculating stock level

[apvit]

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 date quantity
A 2020-01-01 1
B 2020-01-03 10
B 2020-02-02  15
A 2020-02-03 0

desired result:   a calendar showing stock level (=quantity of latest entry per product <= date)  daily   

product date stock level
A 2020-01-01  1
B 2020-01-01  0
A 2020-01-02  1
B 2020-01-02  0
A 2020-01-03  1
B 2020-01-03  10
... ... ...
A 2020-02-25  0
B 2020-02-25  15
.. .. ..

What I tried:

made a date table "Date"

made these Measures:

overall Quantity = SUM(stock[quantity])
quantity per day = CALCULATE([overall Quantity],FILTER(ALLSELECTED('Date'[Date]),'Date'[Date]<=MAX('Date'[Date])))

OK, this sums up quantity.  Not quite what I want.

Thanks for your help

[lbendlin]

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.

[lbendlin]

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]

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

[lbendlin · Accepted solution]

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

[Prov]

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

[apvit]

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?