Create Historical Stock

[Young_G_Han]

  Dear Kudos

I have current stock in by item in a table. It doesn't have any timeline information, it just has today's stock.

I have two other tables that have timelines and transactions both input stock and selling.

How can I create historical stock such as stock quantity by day or month for previous days?

Current Stock + yesterday's shipment - yesterday's input stock = yesterday's stock.

It looks simple but I need to make a graph with the date on the X-axis.

Would it be possible to make by Dax measure?

Please help.

[Young_G_Han]

Stock Table

Let's say today is 2024-01-02

Product     Quantity

A                100

B                 50

Shipment Table

Product     Shipped Date    Quantity

A               2024-01-01           10

A               2023-12-31           10

B               2023-12-30           10

Input Stock Table

Product     Shipped Date    Quantity

A               2024-01-01           30

B               2023-12-31           40

In this case, I want to make a line graph that shows entire stock and of course it should be filtered by product.

If I show expected value by table,

Product        Date             Stock Quantity

A                2024-01-02       100          Current Stock

B                2024-01-02         50          Current Stock

A                2024-01-01       100           After 10 Shipped, 30 input

B                2024-01-01         50           No transaction, no input

A                2023-12-31         80           After 10 Shipped

B                2024-12-31         50           After 40 Shipped

A                2023-12-30         90           No transaction, no input

B                2024-12-30         10           After 10 input

Based on this stock in the graph should be...

In case Daily Stock, it should show sum of each product stock.

In case Monthly Stock, it should show sum of each product stock at the last day of a month.

It is very complicated...

[Ashish_Mathur]

Hi,

I just cannot understand how you arrived at the number in the third table.  Put all information in an MS Excel file and calculate the numbers shown the Balance column of the third table via Excel formulas.   I will try to convert those Excel formulas to DAX formulas.

[Young_G_Han]

Let me explain.

The third table is from my idea.

Let's take the item A.

On day 2023-12-30, 90pcs in stock.

On day 2023-12-31, 10pcs were shipped. So remain stock is 80pcs.

On day 2024-01-01, from 80pcs. 10pcs were shipped and 30pcs input. So remain stock is 100pcs.

On day 2024-01-02. from 100pcs. there is no shipment or input. So still 100pcs are in stock.

This is what I want.

Problem is I have current stock, in this case 100pcs. at 2024-01-02.

I have to create a graph by reverse calculation...

Hope it is clear.

[Ashish_Mathur]

Hi,

I have solved a similar problem in the attached files where closing stock has to be calculated from opening stock, purchases and sales.  Yours is the reverse case.  Please try to adapt the solution to your specific case.

Hope this helps.

[Young_G_Han]

  Dear Ashish

Thank you for your suggestion.

I have reviewed both files and the example1 is close to the solution.

But... In you file, the initial backlog is starting from Dec. 1 2022. and all calculation is creating 2023 opening backlog, revenue, expenses and closing backlog.

I need to start from today and it is closing backlog.

One of my idea is that I can use the entire revenue and bill (in your file) with running until previous month.

This means the entire - running is accumulated value (dates backward).

I am trying but not easy.

If you can help, that would be better...

Anyways, your files helps a lot.

[Ashish_Mathur · Accepted solution]

Hi,

Share the download link of the PBI file.  Ensure that in the Stock Table, there is a Date column.

[Young_G_Han]

Dear Ashish

With your file, I added my idea that the Entire R - until R and the Entire E - until E.

I successfully made all stock information based on date!

Thank you for your great help!

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result in a Table format very clearly.