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.

     

     

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
5 years ago

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'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?