Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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:
Jan
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?
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
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 !!! 😁
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:
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
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!
User | Count |
---|---|
60 | |
22 | |
18 | |
18 | |
16 |
User | Count |
---|---|
86 | |
54 | |
54 | |
38 | |
21 |