How to calculate change in inventory

[asafweis]

Hi all,

I'm creating an inventory dashboard and I need to show the change in inventory for each month.

I have a table that shows current inventory on hand for each time there is a movement in inventory. So if stock is used there is a transaction recorded and if stock is added there is a transaction. 

I figure that I need the <opening stock for the month> + <stock received for the month> - <Closing balance for the month>

Can anyone help with the DAX on this?

Thanks.

[asafweis · Accepted solution]

Thank you for all the suggestions.

The solution was found with the following code:

Measure1

Last Known Inventory =

var maxKnownDate = MAX('FY Dates'[Date])

var lastKnownInventory =

CALCULATETABLE(

    SUMMARIZE(

        DataTable,

         

        DataTable[PartID],

        "LastKnownInventory",

        LASTNONBLANKVALUE(

            'FY Dates'[Date],

            SUM(DataTable[QOH After])

        )

    ),

    FILTER(

        ALLSELECTED('FY Dates'),

        'FY Dates'[Date]<=maxKnownDate

    )

)

return SUMX(lastKnownInventory, [LastKnownInventory])

 

Measure 2

First Known Inventory =

var minKnownDate = MIN('FY Dates'[Date])

var firstKnownInventory =

CALCULATETABLE(

    SUMMARIZE(

        DataTable,

         

        DataTable[PartID],

        "FirstKnownInventory",

        FIRSTNONBLANKVALUE(

            'FY Dates'[Date],

            SUM(DataTable[QuantityOnHand])

        )

    ),

    FILTER(

        ALLSELECTED('FY Dates'),

        'FY Dates'[Date]>=minKnownDate

    )

)

return SUMX(firstKnownInventory, [FirstKnownInventory])

 

Measure 3

Positive Stock Movement = CALCULATE(SUM(DataTable[InventoryQuantityReceived]),DataTable[InventoryQuantityReceived]>0)

 

So stock consumption for the month is:

Stock Consumption = SUMX(DataTable,[First Known Inventory]+[Positive Stock Movement]-[Last Known Inventory])

[PaulDBrown]

Can you share some sample non-confidential data which reflects the data you are working with?

[Anonymous]

Hi @asafweis ,

Please check this if helps:

Cumulative total – DAX Patterns

You might also consider posting sample data and expected output, it would be helpful.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[asafweis]

Thank you, but I can't see a topic that deals with stock consumption for the month. 

[daXtreme]

Here's a list of posts that deal with similar issues: Search - Microsoft Power BI Community

In the Search box you can type in your own query... like, say, "stock balance end of month" and similar.