Opening stock - supply - demand - closing stock

[Vinod_P]

Below is the sameple data and looking for demand( Count of values in demand date column) & Supply (Count of values in the supply date column) , Looking at a view to compare both counts like 1-1 against the fixed jan to dec month on X-axis.

ID NUMBER	Part number	CUSTOMER	DEMAND DATE	SUPPLY DATE
1	1	Vinod	17/08/2022	12/01/2023
2	1	Vinod	17/08/2022	04/05/2023
3	1	Vinod	17/08/2022	27/01/2023
4	1	Vinod	17/08/2022	05/04/2023
5	2	Vinod	01/02/2022	20/01/2023
6	2	Vinod	01/02/2022	20/01/2023
7	2	Vinod	01/02/2022	20/01/2023
8	2	Vinod	01/02/2022	20/01/2023
9	2	Vinod	01/02/2022	10/03/2023
10	2	Vinod	01/02/2022	28/02/2023
11	2	Vinod	01/02/2022	10/03/2023
12	2	Vinod	01/02/2022	05/04/2023
13	2	Vinod	27/04/2022	20/01/2023
14	1	Vinod	21/10/2022	05/04/2023
15	1	Vinod	21/10/2022	05/04/2023
16	1	Vinod	21/10/2022	17/04/2023
17	1	Vinod	21/10/2022	04/05/2023
18	1	Vinod	21/10/2022	17/04/2023

Expecting outcome as below. 

Opening stock = Previous month closing stock

Supply = count of supply date column

Demand = count of demand date column

Closing stock = Opening stock + Supply - Demadn

Thanks in advance 

[v-eqin-msft]

Hi @Vinod_P ,

Here I have some questions about your calculation logic.

1.Opening stock = Previous month closing stock

There are two date columns in your data model, demand date and supply date, which one should be the date column I choose to get previous month data.

There isn't any status in your data model, how can I check whether the stock is closed.

How to get negative numbers in your calculation?

2.Supply = count of supply date column

The number I count the Supply Date on 2023/01 should be 7, how to get 3.

3.Demand = count of demand date column

The number I count the Demand Date on 2023/01 should be 0, how to get 9.

Best Regards,
Rico Zhou

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

[Vinod_P]

Hi , 

Please find the explanation 

1. Better explain with an example if I want opening stock of Jan '23 = (Total supply- total demand + opening stock) of dec '22 technically in theory its the previous month left over stock after all transactions. 

Dec left over stock will carryforward as opening stock for next month. 

For 2 & 3 sorry since I put some random numbers to fill the blanks. 

Formula screenshot FYR

Please let me know if any other clarifications required

[Vinod_P]

Or to still simplify remove opening stock and preious month closing stock should be added to current