Forum Discussion

caseski's avatar
caseski
Helper 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 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?

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.

     

     

     

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community 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
      Helper 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
        Community 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.