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 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.

     

     

     

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.

 

 

 

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