Inventory Running Total

[sy898661]

Hi all!

I am trying to set up my data to make this do what I want it to, but it keeps not doing that!!

I have this pbix file:

https://drive.google.com/file/d/16bRDCCQ1z9UQby2SRNZ-misKplOwuYS_/view?usp=sharing

And the jist of this is we are trying to find a running total over time. The way we set it up, we thought PBI would understand, but its not showing how we want it to show.

So each line item is a different lot, and the action is either Make or Ship. What we THOUGHT we could do was make the Inventory Impact for Make a +1 and the Ship a -1 (or -2) so that we could do SUM(Inventory Impact) and the Make would add to it while the Ship would subtract from it.

but since it is tied to the Date it wont show an OVERALL Total. We want to show how much the inventory levels change in all over time.

What we know:

Product A starting inventory level = 28

Product B = 10

Product C = 1

Can anyone help with this??? 😞

Added: Excel file with desired solution:

https://drive.google.com/file/d/1S5BRSE2Rf7R5Vl07BHB5uApSXyny3WlR/view?usp=sharing

[amitchandak]

From where this initial inventory of 39 will come. Is it 39 for all products?

[sy898661]

Hi @amitchandak I am so sorry I should have explained that wayyy better. I was switching back and forth between my actual file and the simplified version I posted to this thread and so I didnt think to explain it.

So not included in the file data but from what we know initially:

Product A starting inventory level = 28

Product B = 10

Product C = 1

Every time(date occurance) the Inventory Impact is + we want to add that to the initial inventory level, and every time it is - we want to subtract from that.

[Ashish_Mathur]

Hi,

In a simple Table format show your expected result with an explanation.

[sy898661]

Here is an excel file with my desired solution (I only did 2020 but would like for this to go on until the data runs out)

https://drive.google.com/file/d/1S5BRSE2Rf7R5Vl07BHB5uApSXyny3WlR/view?usp=sharing

Thank you!

[PaulDBrown]

@sy898661 

See if this thread can provide insights:

https://community.powerbi.com/t5/Desktop/Need-Expert-Help-Inventory/m-p/941889#M451331

[sy898661]

Thanks @PaulDBrown !!

I will try and look through that some more. Honestly I am so confused because our scenarios are just a little different, but I will try my best. 🙂

[PaulDBrown]

@sy898661 

what I think might be of use is a post in that thread which shows the maths involved in calculating inventory. 

Having said that, let us know if you cannot move forward and we will gladly offer assistance.