How to create stock simulation from running total of average sales number

[Shell]

Hi,

I am going to make simple stock simulation of 4 products(A-D) from the data as shown below.

What I try to do via Power BI is 

1. make measure which is calculated average sales from past sales data

2. Get running total of average sales

3. Calculate using formula below.

     Future stock = [current stock] + [scheduled purchase number] - [ Running total of average sales]

I have calculated 1(average sales) by measure as below but couldn't go to 2 and 3 

 (Average_SalesNumber = CALCULATE(AVERAGE('sales_record'[sales number]),ALLEXCEPT('Date','Date'[Date]))

Could someone kindly let me know how to solve this??

I have made sample data and ideal result by excel as below .

stock

Product_code	Stock
A	200
B	400
C	350
D	500

Average sales

Product_code	Sales_Number
A	50
B	50
C	50
D	30

Purchase

Product_code	Ordef_Volume	Arrival
A	300	1/08/2022
B	150	1/09/2022
D	20	30/09/2022
A	50	1/10/2022
C	40	15/10/2022
A	30	20/11/2022
C	50	15/12/2022
D	20	9/02/2023
B	150	8/11/2022

Ideal result

Calculation image

Prodcut_code	Status	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb
A	Sell		50	50	50	50	50	50	50	50
	Purchase			300		50		30
	Stock	200	150	400	350	350	300	280	230	180
B	Sell		50	50	50	50	50	50	50	50
	Purchase				150		150
	Stock	400	350	300	400	350	450	400	350	300
C	Sell		50	50	50	50	50	50	50	50
	Purchase					40		50
	Stock	350	300	250	200	190	140	140	90	40
D	Sell		30	30	30	30	30	30	30	30
	Purchase				20					20
	Stock	500	470	440	430	400	370	340	310	300

I hope your kind assisit. Thank you.

[Shell]

Hi, 

Thank you for your reply.

Please find the table requested.

Sales data

Date	Prodcut_code	Sales_Number	Price	Sales
1/01/2021	A	50	100	5000
1/01/2021	B	20	50	1000
1/01/2021	D	60	70	4200
1/02/2021	A	30	100	3000
1/03/2021	C	50	150	7500
1/04/2021	D	10	70	700
1/05/2021	A	30	100	3000
1/05/2021	B	50	50	2500
1/05/2021	B	80	50	4000
1/06/2021	A	100	100	10000
1/07/2021	D	20	70	1400
1/07/2021	A	50	100	5000
1/07/2021	B	40	50	2000
1/08/2021	C	10	150	1500
1/09/2021	A	50	100	5000
1/09/2021	B	40	50	2000
1/10/2021	A	80	100	8000
1/10/2021	B	70	50	3500
1/11/2021	A	40	100	4000
1/11/2021	B	40	50	2000
1/11/2021	C	90	150	13500
1/12/2021	A	20	100	2000
1/12/2021	B	60	50	3000

Purchase

* I use only arrival date as I want to know when the stock will arrive to the office, not purchase date.

Product_code	Ordef_Volume	Arrival	Purcahse date
A	300	1/08/2022	1/05/2022
B	150	1/09/2022	1/08/2022
D	20	30/09/2022	1/05/2022
A	50	1/10/2022	1/02/2022
C	40	15/10/2022	1/03/2022
A	30	20/11/2022	1/05/2022
C	50	15/12/2022	1/08/2022
D	20	9/02/2023	17/06/2261
B	150	8/11/2022	1/02/2022

Please let me know if you have anything else to be needed.

Kind regards,

Shell

[Ashish_Mathur]

Hi,

How come there are no dates in the Sales and Purchase tables.  There should definitely be a date column in those tables.  Share as much data as is required to solve the question and show the expected result in a table format.

[Shell]

Hi Ashish,

Thank you for your reply.

Please find the table requested.

Sales data

Date	Prodcut_code	Sales_Number	Price	Sales
1/01/2021	A	50	100	5000
1/01/2021	B	20	50	1000
1/01/2021	D	60	70	4200
1/02/2021	A	30	100	3000
1/03/2021	C	50	150	7500
1/04/2021	D	10	70	700
1/05/2021	A	30	100	3000
1/05/2021	B	50	50	2500
1/05/2021	B	80	50	4000
1/06/2021	A	100	100	10000
1/07/2021	D	20	70	1400
1/07/2021	A	50	100	5000
1/07/2021	B	40	50	2000
1/08/2021	C	10	150	1500
1/09/2021	A	50	100	5000
1/09/2021	B	40	50	2000
1/10/2021	A	80	100	8000
1/10/2021	B	70	50	3500
1/11/2021	A	40	100	4000
1/11/2021	B	40	50	2000
1/11/2021	C	90	150	13500
1/12/2021	A	20	100	2000
1/12/2021	B	60	50	3000

Purchase

* I use only arrival date as I want to know when the stock will arrive to the office, not purchase date.

Product_code	Ordef_Volume	Arrival	Purcahse date
A	300	1/08/2022	1/05/2022
B	150	1/09/2022	1/08/2022
D	20	30/09/2022	1/05/2022
A	50	1/10/2022	1/02/2022
C	40	15/10/2022	1/03/2022
A	30	20/11/2022	1/05/2022
C	50	15/12/2022	1/08/2022
D	20	9/02/2023	17/06/2261
B	150	8/11/2022	1/02/2022

Please let me know if you have anything else to be needed.

Kind regards,

Shell

[Ashish_Mathur]

Hi,

As requested in my previous message, please show the expected result very clearly based on the tables that you have shared.

[Shell]

Hi Ashish,

Please find the result exactly I expect as below.

Prodcut_code	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb
A	200	150	400	350	350	300	280	230	180
B	400	350	300	400	350	450	400	350	300
C	350	300	250	200	190	140	140	90	40
D	500	470	440	430	400	370	340	310	300

And if you should be able to make graph from table above.

Kind regards,

Shell

[Ashish_Mathur]

Hi,

Sorry for asking you for more information but only with this additional information may i be able to come up with a solution.  In an MS Excel workbook, have 3 worksheets - Stock, Sales and Purchases.  In a 4th worksheet, show your expected result (with formulas intact) so that i can translate those excel formulas in the DAX language.

[Shell]

Hi Ashish,

Please find Excel file as requested.

https://docs.google.com/spreadsheets/d/1eSUda4Yz1hADLQ0xykgVSsuT6uYbmlSR/edit#gid=199802600

I add 2 sheets below other than 4 sheets you asked.

- Average_Sales : Calculation to show average sales

- Explanation : How I made the ideal result by excel

Please let me know if you have any question or data you need.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Shell]

Hi Ashish,

Thank you for your great help. This is what I wanted to know.

[Ashish_Mathur]

You are welcome.