Forum Discussion
Calculating stock level
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 twoOutput = CROSSJOIN(Dates,Products)This table will feed your visual.The last thing to do is add the measure for the inventorystock =
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
sBelow is the result, filtered down to only the dates where something is happening
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).
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
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
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?