Calculate inventory per date

[caseski]

I'd like to calculate the inventory per date starting from 2 tables:

TABLE 1_RAW MATERIAL_OUT 

(data are imported from production SQL DB- MES)

It contains thousands of rows as every day there are a lot of picking and represents the consumption of raw material

ITEM

DATE (d/m/y - h/m/s)

QUANTITY OUT (kg)

TABLE 2_RAW MATERIAL_IN

(data are imported from ERP - SQL DB)

ITEM

DATE (d/m/y - h/m/s)

QUANTITY IN (kg)

I have to calculate inventory by date and by item. The problem is that I have to somehow force the data since the inventory is not only the difference between in and out but also the quantity of material already in the warehouse at time zero. Historical data are imported from the databases which also include past years but I would like to start this year by forcing the initial inventory data and from there start with the calculation that you consider purchases (IN) and consumption (OUT).

How can I do?

[Greg_Deckler]

@caseski A little hard to wrap my head around without sample data. So are you saying that once you have a starting point, you would need to, let's say, have a date table. For each date you could create a calculated column that added up all the IN's from the start date until the current date (row) and subtract all the OUT's similarly. Right?

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[caseski]

From the production database I have the quantity of raw material used (table_1 material out).

From the ERP database I have the enter of raw material i.e. purchased quantyt (tabel_2 material in).

I would like to calculate the stock quantity per date for each item-

The stock quantity is:

Quantity at time zero + IN - OUT.

Therefore the table 1 contains OUT.

Table 2 contains IN.

How can I input the stock quantity at time zero? The stock quantity is known but the information is not in the tables. I should create a new input table?

Then I must calculate the stock level. 

TABLE 1 MATERIAL OUT

DATE	HOUR	ITEM	QTY
04/01/2021	10:27:39	BENT019F	3,2
04/01/2021	10:31:02	BENT019F	3,1
04/01/2021	10:47:41	BENT019F	3,4
04/01/2021	10:55:00	BENT019F	3
05/01/2021	09:34:40	BENT019F	3,1
05/01/2021	09:37:48	BENT019F	3,07
05/01/2021	10:09:17	BENT019F	3,04
05/01/2021	10:12:17	BENT019F	3,01
05/01/2021	12:45:37	BENT019F	2,98
05/01/2021	12:48:31	BENT019F	2,95
05/01/2021	14:24:19	BENT019F	2,92
05/01/2021	14:27:02	BENT019F	2,89
05/01/2021	14:53:40	BENT019F	2,86
05/01/2021	14:56:15	BENT019F	2,83
07/01/2021	12:33:04	BENT019F	2,8
07/01/2021	12:40:44	BENT019F	2,77
07/01/2021	12:54:46	BENT019F	2,74
07/01/2021	12:58:06	BENT019F	2,71
07/01/2021	13:34:43	BENT019F	2,68
07/01/2021	13:38:07	BENT019F	2,65
07/01/2021	15:05:07	BENT019F	2,62
07/01/2021	15:08:19	BENT019F	2,59
07/01/2021	15:26:22	BENT019F	2,56
07/01/2021	15:29:34	BENT019F	2,53
08/01/2021	10:51:31	BENT019F	2,5
08/01/2021	10:56:08	BENT019F	2,47
08/01/2021	11:41:13	BENT019F	2,44
08/01/2021	11:44:15	BENT019F	2,41
08/01/2021	13:33:31	BENT019F	2,38
08/01/2021	13:36:36	BENT019F	2,35
08/01/2021	13:42:12	BENT019F	2,32
08/01/2021	13:44:58	BENT019F	2,29
08/01/2021	13:52:01	BENT019F	3,2
11/01/2021	16:46:08	BENT019F	2,98

TABLE 2 MATERIAL_IN

DATE	ITEM	QTY	INV
11/01/2021 00:00	CARB001	32	ACQ
12/01/2021 00:00	CARB005	31	ACQ
12/01/2021 00:00	CARB005	30	ACQ
12/01/2021 00:00	BENT020	31	ACQ
12/01/2021 00:00	BENT020	30	ACQ
12/01/2021 00:00	BENT020	30	ACQ
12/01/2021 00:00	BENT020	30	ACQ
12/01/2021 00:00	BENT020	28	ACQ
12/01/2021 00:00	BENT020	31	ACQ
13/01/2021 00:00	CARB005	30	ACQ
13/01/2021 00:00	CARB005	30	ACQ
13/01/2021 00:00	CARB005	32	ACQ
13/01/2021 00:00	BENT020	30	ACQ
13/01/2021 00:00	BENT020	30	ACQ
13/01/2021 00:00	BENT020	29	ACQ
13/01/2021 00:00	BENT020	30	ACQ
13/01/2021 00:00	BENT020	30	ACQ
13/01/2021 00:00	BENT020	29	ACQ
14/01/2021 00:00	CARB005	32	ACQ
14/01/2021 00:00	CARB005	32	ACQ
14/01/2021 00:00	CARB005	31	ACQ
14/01/2021 00:00	BENT020	30	ACQ
15/01/2021 00:00	CARB005	31	ACQ
15/01/2021 00:00	CARB005	31	ACQ
16/02/2021 00:00	CARB001	31	ACQ
17/02/2021 00:00	CARB005	31	ACQ
17/02/2021 00:00	CARB005	31	ACQ

[v-kkf-msft · Accepted solution]

Hi @caseski ,

I create two new tables to better display the results.

Then create measures:

Per Day = 
CALCULATE(
    SUM(MATERIAL_IN[QTY]),
    FILTER(
        MATERIAL_IN,
        MATERIAL_IN[DATE] = MAX('DATE'[Date])
    )
) - CALCULATE(
        SUM('MATERIAL OUT'[QTY]),
        FILTER(
            'MATERIAL OUT',
            'MATERIAL OUT'[DATE] = MAX('DATE'[Date])
        )
    )

Stock Quantity = 
IF(
    [Per Day] <> BLANK(),
    CALCULATE(
        SUMX( 'DATE', [Per Day] ),
        FILTER(
            ALL('DATE'),
            'DATE'[Date] <= MAX('DATE'[Date])
        ),
        FILTER(
            'ITEM',
            'ITEM'[ITEM] = MAX('ITEM'[ITEM])
        )
    )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[caseski]

Thank you, it works and solve the first problem.

It remains to solve the second problem which is the following.

In the table MATERIAL_IN there aren't the stock quantities at the starting time. Following your solution I've created a Data table starting at 01 January 2021, therefore the measure Per Day calculate the difference between material inbound and material outbound, but don't consider that at the beginning there is a stock quantity for each item. How can consider it?