Calculate inventory for each day

[sritter_sellvin]

Hey all,

i have a problem to calculate the inventory for each day by sum the stock movements.

My structure looks like this:

Item -> Products (60.000 Rows)

ItemBinLog -> Stockmovements (10.000.000 Rows)

ItemBinLogDate -> Datetable from 01.01.2012 to Today

Some Data from the Stockmovements:

ID (of stockmovement)	Storage-bin	Quantity	Item	Movementdate
7217434	49621	120	106844	18.11.2019 00:00:00
7217469	49237	120	106844	18.11.2019 00:00:00
7217499	49239	120	106844	18.11.2019 00:00:00
7217511	49242	120	106844	18.11.2019 00:00:00
7217536	49250	120	106844	18.11.2019 00:00:00
7217697	49632	120	106844	18.11.2019 00:00:00
7217580	56237	132	106844	18.11.2019 00:00:00
7216633	60610	1200	106844	18.11.2019 00:00:00
7218711	60610	-1200	106844	18.11.2019 00:00:00
7220665	46703	1200	106844	18.11.2019 00:00:00
7229082	56237	-1	106844	20.11.2019 00:00:00
7229083	56237	-1	106844	20.11.2019 00:00:00
7231840	46703	-328	106844	20.11.2019 00:00:00
7231839	46702	328	106844	20.11.2019 00:00:00

To calculate the inventory for a day we have to sum all the movements <= the expected date.

The problem is to get the inventory for dates where no movements exists like the 2019-11-19 (19.11.2019)

Thanks for you help

Sebastian

[sritter_sellvin · Accepted solution]

I got the right solution:

CALCULATE(SUM(ItemBinLog[Qty]); FILTER(ALL(ItemBinLogDate); ItemBinLogDate[date] <= MAX(ItemBinLogDate[date])))

[RohiniP-26]

sum= CALCULATE(SUM('Stockmovements'[Quantity]),FILTER('Stockmovements','Stockmovements'[MovementDate]<=EARLIER('Stockmovements'[MovementDate])))

it will work irrespective of presence or absence of date