Inventory history warehouse

[Toppler]

Hello all,
this is my first post and I hope you can help me.

I would like to show the inventory history of our goods.
For this I have a measure the sum of all consumption of our products.
Also I have the current inventory for the product.

Now I need to make a backward calculation, that is, calculate the initial stock with the consumption.

The problem is that the display shows e.g. the following:

11.03.2021 opening stock - consumption on 11.03.
10.03.2021 Opening stock - consumption on 10.03.
09.03.2021 Opening balance - consumption on 09.09.

However, I would like to display it as follows:

03.11.2021 Opening balance - consumption 03/11.
03.10.2021 Outturn 03/11 - Consumption 03/10.
09.03.2021 Result 10.03. - Consumption 09.03.
and so on

I hope you can help me.

Many thanks already.

Greetings
Tobias

[ryan_mayu]

@Toppler 

could you please provide the sample data and the expected output?

[Toppler]

I have a current stock for each product.

I have linked this table with the consumptions.
Here we have the stock entries for each individual product.
I would like to calculate the inventory history backwards.
For example, for the product 30mtet7133.
Here the initial stock is 15.
On March 04, we consumed 2 pieces, so our inventory on March 04 was 17. I would like to map that in a chart. So on 09 November we had 29 pieces in stock.

Currently I am calculating:
Current stock - Measure (sum(menge))

Now for each day the Current stock - consumption on the given day

I want :
Current iventory - last day - day before last- .......

[ryan_mayu]

@Toppler 

it's better provide sample data which has the same structure of your real data, not the screenshot of part of your data. Otherwise, it's very hard to provide you the solution.

[Toppler]

ok next try 🙂 

soltion should look like this 

[ryan_mayu]

@Toppler 

please see the attachment below

[Toppler]

thank you for your answer.
I tried to apply it to my evaluation, now I have another problem.
I have extended the file by a product and now the result is no longer correct, because I have several products in my evaluation.

[ryan_mayu]

@Toppler 

please see the attachment

[Toppler]

ok very nice 🙂 

now my last question. 

can i hide the days when nothing was consumed. these are displayed as 0

[ryan_mayu · Accepted solution]

@Toppler 

have you tried to use filter to remove those data

[Toppler]

I have tried this, but it can happen that this value actually comes to 0 due to consumption.
Now every day that I have no consumption, the value is displayed zero

[ryan_mayu]

@Toppler 

your screenshot is different from what we have discussed. could you please provide sample data and output again?

[amitchandak]

@Toppler , Usually we got onhand like

this you can call as closing 

[Intial On Hand] + CALCULATE(SUM(Table[Purchase]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[consumption]),filter(date,date[date] <=maxx(date,date[date])))

for opening

[Intial On Hand] + CALCULATE(SUM(Table[Purchase]),filter(date,date[date] <maxx(date,date[date]))) - CALCULATE(SUM(Table[consumption]),filter(date,date[date] <maxx(date,date[date])))

[Toppler]

@amitchandak , thanks but doesn't work yet. 

I have only consumption and have created a measure for it.

About the formula (initial stock - consumption) is applied to this formula for each day, so I see unfortunately no course of the stock.

I want a chart that, starting from the initial stock, subtracts the consumptions, that I can see how the stock of products was.