Help with Formulas

[Syndicate_Admin]

Hello, I have the following query:

I have to calculate the inventory in real time for each product ID. I have 3 types of movements with their respective dates/time, counting movements (which force inventory), inbound movements (purchases) and outbound movements (sales). Then the inventory is calculated as follows:

Actual Inventory for each ID = Last stock count + input movements (only those that are dated between the last count and the current date) - output movements (only those that are dated between the last count and current date).

I have a table where I have the counting moves with their date:

and a different table for the rest of the movements

Where the input ones are "IN" and the output ones are "OUT".

I appreciate if anyone can help to calculate the stock in real time. THANK YOU!

Ideally, you'll then end up with a real-time stock for each product ID.

[v-chenwuz-msft · Accepted solution]

Hi @Syndicate_Admin ,

You can create a column via this code:

DIFF = IF([ABC _type]="IN_PROVIDER_PURCHASE",[123 amount],-[123 amount])

Then sum the column.

If i misunderstood you, please let me know.

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.