Formula for calculating available stock per order

[AudreyWen]

Hi,

I need your help. I want to calculate the available stock for sales orders.

Beneath is an example

Stock table:

Article	Total stock
12345	10
23456	15
34567	3
45678	50
56789	5

Sales order table:

Order	Article	delivery quantity	Deliver date	Available stock for order
101010	12345	5	19-2-2020	10
404040	12345	5	22-2-2020	5
707070	12345	5	22-2-2020	0
909090	12345	5	28-2-2020	-5
202020	23456	10	18-2-2020	15
222222	23456	25	20-2-2020	5
303030	34567	1	23-2-2020	3
505050	34567	1	23-2-2020	2
606060	34567	1	28-2-2020	1
808080	45678	10	19-2-2020	50
111111	45678	15	20-2-2020	40
333333	45678	5	22-2-2020	25
444444	45678	10	23-2-2020	20
555555	45678	5	23-2-2020	10
666666	45678	5	28-2-2020	5
777777	56789	2	22-2-2020	5
888888	56789	4	28-2-2020	3
999999	56789	6	1-3-2020	-1

In the column "Available stock for order" I want a calculation that shows how much stock there is available for the sales order. The data that is now in this column, is the result I want. 

I realy cant figure out how i can get this result.

I hope someone can help me with this 😊

Greetings Audrey

[JustJan]

Hi @AudreyWen ,

I did not provide you with the relationship between my tables. 

And my Index is not more than a sequencenumber on the table. 

If you change the last lines of the measure to, for instance: 

var result = currentArticle    //StockLevel - x

return result

You can check if the correct artikelnummer is used.

Jan 

Jan 

[AudreyWen]

Hi again Jan,

This time i've tried it in the simple test file and there it does work. Now i don't know why it keeps on running in the big work file. Maybe because there are many rows?

I have an column (Status levering) with a status of an row. In the page filter in the report i have filtered on only the rows with status "open" or "in levering" this reduce the number of rows a lot.

Can this be my problem?

[JustJan]

Hi Audrey, 

The measure does calculate a sort of running total. I have a similar problem where I need to calculate a running total over more than 50.000 records. The calculation uses all the computers internal memory (up to 12GB). 

So you could check you memory consumption in the task manager and see if that is also the problem. 

The only way I can think of at the moment is to calculate the totals outside PBI, but then you will loose a lot of the dynamics of a dashboard.

Not too sure if page filter are part of the issue, I'll check it on my data model too. 

Jan 

[AudreyWen]

Hi Jan,

I only need the measure on those rows. Now i have made a sort of "between solution". In the report with orders i filter on the status "Open" or "In levering". Than i have the only rows i need for this measure. This report i export to an .CSV file. This .CSV file is now an Table in my file. That table i use now in a report and now it wordt great. But the "between solution" is not ideal because i have to do extra steps. I hope we can find a solution in wich the measure knows directly the rows that are needed. 

But still i'm very happy !!! 😁

[AudreyWen]

Hi Jan,

Sorry, i couldn't test it this weekend.

I have made the relationship between those 2 and another index column (this time in the query).

Still it doesn't work. I have tried your suggestion with changing the last row in the messure to:

var result = currentArticle    //StockLevel - x

return result

And this is not a good result, I don't get the current article number but 0,00 with every row in the table.

Can you please help me again?

[JustJan · Accepted solution]

 hi @AudreyWen ,

It is best to add an index column to your order column. That makes it much easier to deal with orders on the same date. 

I think this is the result you are looking for. 

The measure is in table is:

StockLevel BEFORE = 
var currentArticle = if (HASONEVALUE(StockOrders[Article]), min(StockOrders[Article]),0)
var StockLevel = CALCULATE(sum(Stock[Total stock]) , Stock[Article] = currentArticle)
var x = 
CALCULATE(
    SUM('StockOrders'[delivery quantity]),
 StockOrders[Article] = currentArticle, 
    FILTER(
        ALL('StockOrders'),
        'StockOrders'[Index]< MAX('StockOrders'[Index])
    )
)
var result = StockLevel - x
return result

If you want th stock level AFTER, just change this line: 

        'StockOrders'[Index]< MAX('StockOrders'[Index])

to 

        'StockOrders'[Index]<=MAX('StockOrders'[Index])

hope this helps

Jan 

[AudreyWen]

Hi Jan,

I have used your messure in my report but it keeps running like it doesn't know what to do. I have changed al the tables and columns in the meassure to the real ones.

StockLevel BEFORE =
var currentArticle = if (HASONEVALUE(Schedullines[Artikelnr]); min(Schedullines[Artikelnr]);0)
var StockLevel = CALCULATE(sum(AvStock[LBKUM]); AvStock[Artikelnr.] = currentArticle)
var x =
CALCULATE(
SUM(Schedullines[Bevestigde hoeveelheid (BMENG)]);
Schedullines[Artikelnr] = currentArticle;
FILTER(
ALL(Schedullines);
Schedullines[Indexvrrd]< MAX(Schedullines[Indexvrrd])
)
)
var result = StockLevel - x
return result

can you tell me if i did anything wrong?

Thanx!