Future inventory level

[carmae]

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?

[gregorschuerer]

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

[carmae]

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.

[Anonymous]

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 Team

If 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

[carmae]

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

[Anonymous · Accepted solution]

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 Team

If 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

[carmae]

Working great, thank you veeeeeery much!!

[Irwan]

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.

[carmae]

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! 😄