Cumulative stock total with initial data, previsionnal usage and orders

[PierreT]

Hello,

I have been trying for a while to create an area chart able to display the cumulative sum of :

Initial stock - previsionnal usage + previsionnal orders.

I have checked various posts (namely from this community) and youtube videos, trying to use their solutions but in vain, I can't seem to see the issue.

Here is my model :

my date table is [working table] and is writen as follows :

Date table operational =
ADDCOLUMNS(
CALENDARAUTO() ,
"Année" , YEAR( [Date] ) ,
"Num Mois" , MONTH( [Date] ) ,
"Mois" , FORMAT( [Date] , "MMMM" ) ,
"Num Trimestre" , FORMAT( [Date] , "TQ" ) ,
"Trimestre" , FORMAT( [Date] , "Tri\me\stre Q" ) ,
"Trimestre Année" , FORMAT( [Date] , "TQ YYYY" ) ,
"Année Trimestre" , FORMAT( [Date] , "YYYY TQ" ) ,
"Mois Année" , FORMAT( [Date] , "MM YYYY" ) ,
"Année Mois" , FORMAT( [Date] , "YYYY MM" )
)

SAP Table :

Stock table (no duplicate in reference column):

Material	Stock
reference 1	Value (integer) 1
reference 2	Value (integer) 2

Measures :

Quantité = sum(SAP[Quantity open])           
//sum of the column in SAP containing the stock indiscriminately



Stockart = SELECTEDVALUE('Stock table'[stock])               
//value of stock from stock table (supposed to be unique)



QuanteMoins = calculate([Quantité],SAP[Object type EA]="Reservation Item")
//quantity of negative values from SAP table, i.e. previsionnal usage



QuantitePlus = CALCULATE([Quantité],SAP[Object type EA]="Purchase Document Schedule Line")        
//quantity of positive values from SAP table, i.e. previsionnal orders



StockEvolution = [Stockart]+[QuantitePlus]-[QuanteMoins]           
//Global calculation used for graph

Graph table : The sum appears to do each line the sum [stock] - [usage]+ [order] instead of only adding the stock at the start and then use the result to calculate the following line.

Using this :

test sum = TOTALMTD([Stockart]+[QuantitePlus]-[QuanteMoins], 'Date table operational'[Working Date])

yiels the same results but showing it for each day.

Area chart : displays the same as the graph table

The expected results should be :

10/11/2023		561834
15/12/2023		531834
10/01/2024		522935
15/01/2024		519385
15/01/2024		510496
05/02/2024		506934
07/02/2024		498074
26/02/2024		494474
28/02/2024		485607
01/03/2024		482044
07/03/2024		473187
14/03/2024		677640
14/03/2024		668744

(the second column is to make the result more visible)

Please, can anyone tell me if they see the problem?

[amitchandak]

@PierreT , We usually use Cummulative like

Qunatity on hand = [Stockart] + CALCULATE([QuantitePlus],filter(allselected(Date),Date[Date] <=max(Date[Date]))) -CALCULATE([QuanteMoins],filter(allselected(Date),Date[Date] <=max(Date[Date])))

or you calculate initial on hand like

Inventory / OnHand
CALCULATE(firstnonblankvalue('Date'[Month],sum(Table[Intial Inventory])),all('Date')) +

CALCULATE(SUM(Table[QuantitePlus]),filter(all(date),date[date] <=maxx(date,date[date]))) -

CALCULATE(SUM(Table[QuantiteMinus]),filter(all(date),date[date] <=maxx(date,date[date])))

Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw