Forum Discussion
Future inventory level
Hi everyone.
I need to create a report in PowerBI that shows the future evolution of the inventory level of a specific item, given the initial stock, forecasted sales, and planned purchase orders.
For simplicity, let's assume I have only one item to manage. I have:
- A table with the initial stock as of today.
- A table with the forecasted sales quantities for each future day.
- A table with the quantities of incoming material from suppliers for each future day.
I combined these tables, treating the initial stock and purchases as positive movements and sales as negative movements.
The final table looks like this:
Date Item Movement Type Quantity
| 21/08/2024 | Item1 | Initial Stock | 10 |
| 22/08/2024 | Item1 | Forecasted Sales | -3 |
| 23/08/2024 | Item1 | Forecasted Sales | -4 |
| 24/08/2024 | Item1 | Purchase | 5 |
| 25/08/2024 | Item1 | Forecasted Sales | -2 |
| 26/08/2024 | Item1 | Forecasted Sales | -4 |
| 27/08/2024 | Item1 | Forecasted Sales | -3 |
| 28/08/2024 | Item1 | Forecasted Sales | -4 |
| 29/08/2024 | Item1 | Forecasted Sales | -6 |
| 30/08/2024 | Item1 | Forecasted Sales | -2 |
| 31/08/2024 | Item1 | Purchase | 20 |
| 01/09/2024 | Item1 | Forecasted Sales | -2 |
| 02/09/2024 | Item1 | Forecasted Sales | -2 |
| 03/09/2024 | Item1 | Forecasted Sales | -3 |
| 04/09/2024 | Item1 | Purchase | 5 |
| 05/09/2024 | Item1 | Forecasted Sales | -5 |
| 06/09/2024 | Item1 | Forecasted Sales | -3 |
| 07/09/2024 | Item1 | Forecasted Sales | -2 |
| 08/09/2024 | Item1 | Forecasted Sales | -5 |
| 09/09/2024 | Item1 | Forecasted Sales | -1 |
The simplest solution is to sum the quantity column day by day. The problem, as you might guess, occurs starting from 27/08/2024, when the forecasted stock goes negative because the sum of the negative movements up to that date exceeds the sum of the positive movements.
At this point, my requirement is to show a stock of zero and not a negative value. This is straightforward because it only requires a simple IF statement.
The problem I am unable to solve arises when the stock becomes positive again due to an incoming supply. Specifically, on 31/08/2024, the stock returns to 20 units. The next day it will be 18 (20-2), the day after that it will be 16 (20-2-2), then 13 (20-2-2-3), and so on.
Unfortunately, the calculated measure, as mentioned before, sums the theoretical sales on the days when the actual stock was zero, when it should instead ignore them.
Any suggestions on how I could solve this in DAX?
Hi, carmae
Yes, if you want to group and accumulate data, you only need to go one more step to group by Item.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdIxDsMgDIXhuzCnAmxCyBF6hihjx269v8ryiBKePaJPWGD9xxHev883hyWkFiVHSVL6IadwLncT2Esn02FlsgJbJ1rHNZmsOiM35ynNubcPq0/TZL9Fx1rksZY9pmzd6yaOqfWHbtbKOl0rm606IzfnKc2Zea0sDxOnFhirBcZqgZGvg1gtMFYLjNUCY7XAWC3i1AJjtYhTC4zVAmO1wKyVGbXAWC0wVguM1QK71XL+AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, #"Date Item" = _t, Quantity = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Date Item", type date}, {"Quantity", Int64.Type}}), FX = (values as list) as list => let GRTList = List.Generate( () => [GRT = values{0}, i = 0], each [i] < List.Count(values), each let nextGRT = if [GRT] > 0 then [GRT] + values{[i] + 1} else values{[i] + 1} in [GRT = nextGRT, i = [i] + 1], each [GRT] ) in GRTList, // Group by Item and apply transformations Grouped = Table.Group( #"Changed Type", {"Item"}, { {"Transformed", each let dateitemlist = List.Buffer([Date Item]), quantitylist = List.Buffer([#"Quantity"]), result = Table.FromColumns( {dateitemlist, quantitylist, List.Transform(FX(quantitylist), each if _ < 0 then 0 else _)}, {"Date Item", "Quantity", "Output"} ) in result } } ), #"Expanded Transformed" = Table.ExpandTableColumn(Grouped, "Transformed", {"Date Item", "Quantity", "Output"}, {"Date Item", "Quantity", "Output"}) in #"Expanded Transformed"Best Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
8 Replies
Hi, carmae
Thanks for Irwan reply. You can try the following M language in Power Query.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc/LCcAwDAPQXXxOiT/5zlKy/xoNtFEg9fUhI+u+iVtUicqaKJAwjfCaLrsMZrAES8syKCOmsOKcVqeiObkOK8uM/x2GGfrN6JHlzE1Tx+z8Zdo5bdKetq04p9WpaM7tniY0xgM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date Item" = _t, Quantity = _t]), #"Changed Type" = Table.TransformColumnTypes( Source, {{"Date Item", Int64.Type}, {"Quantity", Int64.Type}} ), FX = (values as list) as list => let GRTList = List.Generate( () => [GRT = values{0}, i = 0], each [i] < List.Count(values), each let nextGRT = if [GRT] > 0 then [GRT] + values{[i] + 1} else values{[i] + 1} in [GRT = nextGRT, i = [i] + 1], each [GRT] ) in GRTList, dateitemlist = List.Buffer(#"Changed Type"[Date Item]), quantitylist = List.Buffer(#"Changed Type"[Quantity]), result = Table.FromColumns( {Source[Date Item], Source[Quantity], List.Transform(FX(quantitylist), each if _ < 0 then 0 else _)}, {"Date Item", "Quantity", "Cumulative value"} ), #"Changed Type1" = Table.TransformColumnTypes(result,{{"Date Item", type date}, {"Quantity", Int64.Type}, {"Cumulative value", Int64.Type}}) in #"Changed Type1"Best Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Here's the table with the additional column to manage more than a single Item.
As said your solution works like a charm, I just need now to make it works with multiple items.
I suppose I should use something like Group rows, right?
Thanks again.
ItemDate ItemQuantity
Item1 08/21/2024 10 Item1 08/22/2024 -3 Item1 08/23/2024 -4 Item1 08/24/2024 5 Item1 08/25/2024 -2 Item1 08/26/2024 -4 Item1 08/27/2024 -3 Item1 08/28/2024 -4 Item1 08/29/2024 -6 Item1 08/30/2024 -2 Item1 08/31/2024 20 Item1 09/01/2024 -2 Item1 09/02/2024 -2 Item1 09/03/2024 -3 Item1 09/04/2024 5 Item1 09/05/2024 -5 Item1 09/06/2024 -3 Item1 09/07/2024 -2 Item1 09/08/2024 -5 Item1 09/09/2024 -1 Item2 08/21/2024 10 Item2 08/22/2024 -3 Item2 08/23/2024 -4 Item2 08/24/2024 5 Item2 08/25/2024 -2 Item2 08/26/2024 -4 Item2 08/27/2024 -3 Item2 08/28/2024 -4 Item2 08/29/2024 -6 Item2 08/30/2024 -2 Item2 08/31/2024 20 Item2 09/01/2024 -2 Item2 09/02/2024 -2 Item2 09/03/2024 -3 Item2 09/04/2024 5 Item2 09/05/2024 -5 Item2 09/06/2024 -3 Item2 09/07/2024 -2 Item2 09/08/2024 -5 Item2 09/09/2024 -1 Hi, carmae
Yes, if you want to group and accumulate data, you only need to go one more step to group by Item.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdIxDsMgDIXhuzCnAmxCyBF6hihjx269v8ryiBKePaJPWGD9xxHev883hyWkFiVHSVL6IadwLncT2Esn02FlsgJbJ1rHNZmsOiM35ynNubcPq0/TZL9Fx1rksZY9pmzd6yaOqfWHbtbKOl0rm606IzfnKc2Zea0sDxOnFhirBcZqgZGvg1gtMFYLjNUCY7XAWC3i1AJjtYhTC4zVAmO1wKyVGbXAWC0wVguM1QK71XL+AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, #"Date Item" = _t, Quantity = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Date Item", type date}, {"Quantity", Int64.Type}}), FX = (values as list) as list => let GRTList = List.Generate( () => [GRT = values{0}, i = 0], each [i] < List.Count(values), each let nextGRT = if [GRT] > 0 then [GRT] + values{[i] + 1} else values{[i] + 1} in [GRT = nextGRT, i = [i] + 1], each [GRT] ) in GRTList, // Group by Item and apply transformations Grouped = Table.Group( #"Changed Type", {"Item"}, { {"Transformed", each let dateitemlist = List.Buffer([Date Item]), quantitylist = List.Buffer([#"Quantity"]), result = Table.FromColumns( {dateitemlist, quantitylist, List.Transform(FX(quantitylist), each if _ < 0 then 0 else _)}, {"Date Item", "Quantity", "Output"} ) in result } } ), #"Expanded Transformed" = Table.ExpandTableColumn(Grouped, "Transformed", {"Date Item", "Quantity", "Output"}, {"Date Item", "Quantity", "Output"}) in #"Expanded Transformed"Best Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
hello carmae
I might be misunderstood your need, but let me offer some ways to enlighten your problem.
if you need to show sum of each 'Type', then matrix visual might be your answer.
besides that, what i got from your description, looks like you want to do cumulative sum of 'Quantity' but the value become zero if sum value is negative.
Cumulative =
var _Sum=
SUMX(
FILTER(
'Table',
'Table'[Date Item]<=EARLIER('Table'[Date Item])
),
'Table'[Quantity]
)
Return
IF(
_Sum<0,
0,
_Sum
)Hope this will help.
Thank you.
Thanks for your reply. I'll try to better explain my issue: on 30/08 the quantity is zero. Then on 31/08 20 units get added to inventory. Then on 01/09 2 units are forecasted to be sold. So the quantity on 01/09 should be 18, not 5.
Maybe it's clearer if I explain that we do not keep back orders. So forecasted sales on days with zero inventory should not cumulate.
Hope I managed to explain myself, English is not my native tongue, as you can easily guess! 😄
Seems to work like magic! Unfortunately I'm not that good in M so I should study this piece of code.
Would you be so kind to help me go further? Since I will do this for more than one item how should I modify this to work with several items.
As an example I think you could add a column "Item code", duplicate rows and assign "Item1" to half of them and "Item2" to the remaining.Thanks again, you're helping me a lot.
C.
I'm stuck with exactly the same problem. carmae have you ever figured out how to do this in DAX? Thanks a lot in advance
