Calculate Stock and demand based on material each day - Stock Planning

[aoncirto]

Aloha all, 🤙

After reading some topic, watched some youtubes and try talks with chatGPT. Still cant get the correct result. 😞 

I have multiple tables and one final in my powerBi report. the Final table have following items: 

Date	Part_Number	Demand	Stock
2023-10-01	4ABC588-AD	10	25
2023-10-02	4ABC588-AD	20	25
2023-10-03	4ABC588-AD	15	25

The Column[Stock] is same for all Parts because i downloaded from ERP system(SAP) every Monday and connected to Part Number( that is the reason the same numbers) 

In Column[Part_Number] - there will be multiple parts not only one. 

What is my dream to have: 

Spoiler

Date	Part_Number	Demand	Stock	Stock_Demand
2023-10-01	4ABC588-AD	10	25	15
2023-10-02	4ABC588-AD	20	25	-5
2023-10-03	4ABC588-AD	15	25	-20

behind the calculation: 

Take the first date of the one Part number and calculate [Stock] - [Demand] = 15
Take the result (15) - [Demand] = -5
(-5) - [Demand] = -20

etc.

Thank you in advance for any ideas

[parry2k]

@aoncirto if you visualize it by date, you will get the correct result. Make sure you are adding a measure not a calculated column.

[aoncirto]

Hello @parry2k, 
That was the issue about calc. column and measure. You have right, its works. Thank you so much. 

How it works the measure(the main one)

Calculate and SUM function will like summarize all the values under "Demand" Column and Filter just return the value each day? 

Once again Thank you

[parry2k]

@aoncirto how you are visualizing it? What columns you are using in the visual?

[aoncirto]

The visualization Is not done yet. After the data will be ready.

[aoncirto]

Hello @parry2k , 

I already have the "Calendar" table, but your solution not works the Column "Balance Stock" is -20 for all rows. I need to calculate each row. 

Thank you

[parry2k · Accepted solution]

@aoncirto 

As a best practice, add a date dimension in your model and use it for time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools. Check the related videos on my YT channel

Add Date Dimension
Importance of Date Dimension
Mark date dimension as a date table - why and how?
Time Intelligence Playlist

Once you have date dimension, add following measures:

Total Demand = 
CALCULATE ( 
   SUM ( Table[Demand] ),
   FILTER ( 
      ALL ( 'Calendar'[Date] ),
      'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) 
   )
)

Total Stock = MAX ( Table[Stock] )

Balance Stock = [Total Stock] - [Total Demand] 

[aoncirto]

Hello @parry2k, 

Its there way how to just start the measure calculate from "last Monday" to "next Monday" in current week. 

 I try this but do not works 😕 

MeasureStock_Monday = 
CALCULATE(MAX([Stock_Sum],FILTER('Calendar','Calendar'[Day Name] = "Monday")))

Thank you so much