Forum Discussion

caseski's avatar
caseski
Icon for Helper I rankHelper I
5 years ago
Solved

Calculate inventory per date

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 a...
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    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's avatar
caseski
Icon for Helper I rankHelper I
5 years ago

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

 

DATEHOURITEMQTY
04/01/202110:27:39BENT019F3,2
04/01/202110:31:02BENT019F3,1
04/01/202110:47:41BENT019F3,4
04/01/202110:55:00BENT019F3
05/01/202109:34:40BENT019F3,1
05/01/202109:37:48BENT019F3,07
05/01/202110:09:17BENT019F3,04
05/01/202110:12:17BENT019F3,01
05/01/202112:45:37BENT019F2,98
05/01/202112:48:31BENT019F2,95
05/01/202114:24:19BENT019F2,92
05/01/202114:27:02BENT019F2,89
05/01/202114:53:40BENT019F2,86
05/01/202114:56:15BENT019F2,83
07/01/202112:33:04BENT019F2,8
07/01/202112:40:44BENT019F2,77
07/01/202112:54:46BENT019F2,74
07/01/202112:58:06BENT019F2,71
07/01/202113:34:43BENT019F2,68
07/01/202113:38:07BENT019F2,65
07/01/202115:05:07BENT019F2,62
07/01/202115:08:19BENT019F2,59
07/01/202115:26:22BENT019F2,56
07/01/202115:29:34BENT019F2,53
08/01/202110:51:31BENT019F2,5
08/01/202110:56:08BENT019F2,47
08/01/202111:41:13BENT019F2,44
08/01/202111:44:15BENT019F2,41
08/01/202113:33:31BENT019F2,38
08/01/202113:36:36BENT019F2,35
08/01/202113:42:12BENT019F2,32
08/01/202113:44:58BENT019F2,29
08/01/202113:52:01BENT019F3,2
11/01/202116:46:08BENT019F2,98

 

 

 

TABLE 2 MATERIAL_IN

 

DATEITEMQTYINV
11/01/2021 00:00CARB00132ACQ
12/01/2021 00:00CARB00531ACQ
12/01/2021 00:00CARB00530ACQ
12/01/2021 00:00BENT02031ACQ
12/01/2021 00:00BENT02030ACQ
12/01/2021 00:00BENT02030ACQ
12/01/2021 00:00BENT02030ACQ
12/01/2021 00:00BENT02028ACQ
12/01/2021 00:00BENT02031ACQ
13/01/2021 00:00CARB00530ACQ
13/01/2021 00:00CARB00530ACQ
13/01/2021 00:00CARB00532ACQ
13/01/2021 00:00BENT02030ACQ
13/01/2021 00:00BENT02030ACQ
13/01/2021 00:00BENT02029ACQ
13/01/2021 00:00BENT02030ACQ
13/01/2021 00:00BENT02030ACQ
13/01/2021 00:00BENT02029ACQ
14/01/2021 00:00CARB00532ACQ
14/01/2021 00:00CARB00532ACQ
14/01/2021 00:00CARB00531ACQ
14/01/2021 00:00BENT02030ACQ
15/01/2021 00:00CARB00531ACQ
15/01/2021 00:00CARB00531ACQ
16/02/2021 00:00CARB00131ACQ
17/02/2021 00:00CARB00531ACQ
17/02/2021 00:00CARB00531ACQ

 

 

 

v-kkf-msft's avatar
v-kkf-msft
Icon for Community Support rankCommunity Support
5 years ago

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's avatar
    caseski
    Icon for Helper I rankHelper I
    5 years ago

    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?