Warehouse in specific date

[Anonymous]

Hi everyone,

first post in the comunity

I need to create a graph for warehouse during time. I have warehouse situation in this moment and all the Sell Out and Sell In of the past day. With MasterCalendar I have create calendar from min and max date present. Now I need to calculete the qty of warehouse in that specific date. I try with RangeSum and Above but I have the limit of row sorting and filter on row... Don't work fine.

For exemple warehouse

Item	Quantity
CODE_A	3
CODE_B	4

Movements

Sell	Item	Date	Quantity
IN	CODE_A	12/08/2018	+ 1
OUT	CODE_A	12/08/2018	- 2
OUT	CODE_A	10/08/2018	- 1
OUT	CODE_B	11/08/2018	- 1
OUT	CODE_B	10/08/2018	- 3

I need something like this:

Item	Date	Quantity
CODE_A	12/08/2018	4
CODE_A	11/08/2018	4
CODE_A	10/08/2018	5
CODE_B	12/08/2018	4
CODE_B	11/08/2018	5
CODE_B	10/08/2018	8

[petter]

I think an As-Of Table in your data model should work well for you:

The As-Of Table

[jheasley]

so another option would be to add a 3rd dimension to the sell table for the start date of each month. your entry for december may look like this:

Sell	item	date	quantity
Start	CODE_A	12/08/2018	3
IN	CODE_A	12/08/2018	1
out	CODE_A	12/08/2018	-2

if you have the start for each date, you can then effectively calculate the sum(quantity) for any given date.  as an added side effect, you can use very simple set analysis to also determine in/out/start points for aggregated time ranges.

[Anonymous]

Hi all,

I try both the solutions but I foun easyer solution.

I have create Master Calendar and joined with Warehouse an sell IN & OUT groupped by date. Ordered this table for ITEM and DATE desc so I create a calculed field like this:

if(Previous(ITEM_ID) = ITEM_ID, (Peek(QTY_PROG) + [QTY_OUT] - [QTY_IN]), (QTY_WARE+ [QTY_OUT] - [QTY_IN])) as 'QTY_PROG'

Thanks everybody