Calculating Projected Stock On Hand maintaining less than or equal zero as zero

[ZENMarkD]

Hi All,
I’ve been asked to calculated a forward inventory projection that does not go into negative stock, i.e. when the cumulative stock movement total is less than or equal to zero the stock on hand should be zero.
We want the forecast to reflect what is physically in our DC so when something gets to zero then three days later we receive 1000 units, stock should be 1000 and not subtract the daily forecast for those days. This is what my current report is doing.

The above shows the straight cumulative projection which is just a SUM of the Expected Change measure. Technically correct, however, we need to eliminate the negative SOH.

I’ve got it to a stage where if the previous day’s total is <= 0 then add 0 + Expected Change, usually the daily forecast. I’m okay with that.
The issue I have is the day after a positive change,as you will see around 22/4/22 is when there is a positive change of 2244 but then next day it drops right down to 645 when all I want to see is 2088 (2244 - 156).
Here are the two measures:
Cumulative Projection = CALCULATE( [Expected Change],
FILTER( ALLSELECTED( Dates[Date] ),
Dates[Date] <= MAX( Dates[Date] ) ) )

Modified SOH Projection =
VAR ExpectedChange = CALCULATE( [Expected Change],
FILTER(
ALL( Dates ),
Dates[Date] <= MAX( Dates[Date] )
)
)
VAR LatestChange = CALCULATE( [Expected Change],
FILTER(
ALL( Dates ),
Dates[Date] = MAX( Dates[Date] )
)
)
VAR ExpectedChange_1 = CALCULATE( [Expected Change],
FILTER(
ALL( Dates ),
Dates[Date] < MAX( Dates[Date] )
)
)

VAR SOHProjection = SWITCH( TRUE(),
ExpectedChange_1 <= 0,
0 + LatestChange,
ExpectedChange_1 + LatestChange )

return
SOHProjection

I am stumped! I’m sure there has to be a simpler way but I just cannot solve this. Please help!!

I have attached a super dumbed down pbix file which should be enough to solve the issue.

Sample Projected SOH.pbix (134.5 KB)

Thanks so much in advance!

[jafernandezpuga]

Hello @ZENMarkD ,
I found a solution creating calculated columns but I want to change it to do it with measures.
Regards

[ZENMarkD]

That’s great! Are you able to share what you’ve done so far? I may be able to use it until a version with measures is found, or even work that part out myself.

[jafernandezpuga]

Hi @ZENMarkD,
I share the solution that I have found based on the creation of two calculated columns in the SampleStock table:

Accumulated Stock Movements:

Cum. StockMovement =
CALCULATE(
[Expected Change],
FILTER(
SampleStock,
SampleStock[Date] <= EARLIER( SampleStock[Date] )
)
)
Previous accumulated:

Prev. Cum. StockMovement =
CALCULATE(
[Expected Change],
FILTER(
SampleStock,
SampleStock[Date] < EARLIER( SampleStock[Date] )
)
)

This is the measure created:

Modified SOH Projection (Calculated Column) =
VAR _CurrentDate = MAX( Dates[Date] )
VAR _PrevCumStockMovement = SELECTEDVALUE( SampleStock[Prev. Cum. Stock Movement] )
VAR _CurrentCumStockMovement = SELECTEDVALUE( SampleStock[Cum. Stock Movement] )
VAR _CurrentStockMovement = SELECTEDVALUE( SampleStock[Stock Movement] )

VAR _LastDatewithPrevCumStockMovementNegative = CALCULATE(
MAX(SampleStock[Date] ),
FILTER(
ALL( SampleStock ),
SampleStock[Date] <= _CurrentDate &&
SampleStock[Cum. Stock Movement] > 0 &&
SampleStock[Prev. cum Stock Movement] < 0
)
)

VAR _CumProjection = CALCULATE(
[Cumulative Projection],
FILTER(
ALL( SampleStock ),
SampleStock[Date] >= _LastDatewithPrevCumStockMovementNegative &&
SampleStock[Date] <= _CurrentDate
)
)

VAR _Result = SWITCH(
TRUE(),
_PrevCumStockMovement < 0,
_CurrentStockMovement,
ISBLANK( _LastDatewithPrevCumStockMovementNegative ),
_CurrentCumStockMovement,
_CumProjection
)

RETURN
_Result

If the previous accumulated value is negative, it returns the stock movement.
We look for the last date in which the accumulated is positive and the previous accumulated is negative.
If it does not exist, the accumulated is returned and if it exists, the [Cum. Projection] between that date and the current date.

The measure performed without considering the calculated columns is called Modified SOH Projection (Measure). To this extent I am unable to calculate _LastDatewithPrevCumStockMovementNegative correctly.

Modified SOH Projection (Measure) =
VAR _CurrentDate =
MAX( Dates[Date] )
VAR _PrevCumStockMovement = CALCULATE(
[Expected Change],
FILTER(
ALL( Dates[Date] ),
Dates[Date] < _CurrentDate
)
)
VAR _CurrentCumStockMovement = CALCULATE(
[Expected Change],
FILTER(
ALL( Dates[Date] ),
Dates[Date] <= _CurrentDate
)
)
VAR _CurrentStockMovement = SELECTEDVALUE( SampleStock[Stock Movement] )
VAR _LastDatewithPrevCumStockMovementNegative = CALCULATE(
MAX(SampleStock[Date] ),
FILTER(
ALL( SampleStock ),
SampleStock[Date] <= _CurrentDate &&
_CurrentCumStockMovement > 0 &&
_PrevCumStockMovement < 0
)
)

VAR _CumProjection = CALCULATE(
[Cumulative Projection],
FILTER(
ALL( SampleStock ),
SampleStock[Date] >= _LastDatewithPrevCumStockMovementNegative &&
SampleStock[Date] <= _CurrentDate
)
)
VAR _Result = SWITCH(
TRUE(),
_PrevCumStockMovement < 0,
_CurrentStockMovement,
ISBLANK( _LastDatewithPrevCumStockMovementNegative ),
_CurrentCumStockMovement,
_CumProjection
)

RETURN
_Result

Regards,

Sample Projected SOH_JAFP.pbix (144.3 KB)

[ZENMarkD]

Hi @jafernandezpuga ,
I tried your suggestions but unfortunately no luck. This was partially my fault, as I should have provided you with a sample dataset containing more than one product and location.
I tried a variation of your formulas in a measure that worked partially, until the CumStockMovement produced too big of a negative stock movement causing a false Last Negative Date. So it reset to zero from there which was incorrect:

Here was how far I got with the measure:
New SOH Projection =
VAR LastNegativeDate = CALCULATE(
MAX( Dates[Date] ),
FILTER( ALL( Dates ),
Dates[Date] < MAX( Dates[Date] ) &&
[Prev. Cum. Stock Movement] <= 0 || ISBLANK( [Prev. Cum. Stock Movement] ) &&
[Cum. Stock Movement] <= 0 ) )
VAR NonZeroChange = CALCULATE( [Expected Stock Change],
FILTER( ALL( Dates ),
Dates[Date] >= LastNegativeDate &&
Dates[Date] <= MAX( Dates[Date] ) ) )

VAR ChangeYesterday = CALCULATE( NonZeroChange,
DATEADD( Dates[Date], -1, DAY ) )

return
SWITCH( TRUE(),
[prev. Cum. Stock Movement] <= 0 && [Cum. Stock Movement] > 0,
NonZeroChange,
[prev. Cum. Stock Movement] <= 0 && [Cum. Stock Movement] <= 0,
0,
[Cum. Stock Movement] > 0 && [prev. Cum. Stock Movement] > 0,
NonZeroChange )

I guess it’s back to the drawing board! I’ll keep trying.

Thanks for your help.

[ZENMarkD]

Hi @Melissa,
I’m hoping you could help me out also. I currently have a Projected SOH calculation that is working fine using the method you suugested in this post:

Forecast / Project forward my stock inventory value

Hi Dan, In my case I had the following 5 tables that I transformed and finally combined in Power Query: Purchaseorder lines (qty+) Salesorder lines (qty -) Serviceorder lines (qty -) Production consumption lines (qty - ) Production output lines (qty +) So I ended up with a single “Stock Movements” table that could easily be summed: Expected stock change = SUM( 'Your table name'[Quantity] ) As for the Stock projection, I hope this simple example will help: [Movements] If you place the SUM…

However as mentioned above, I have been tasked with modifying it so that it doesn’t go into negative, i.e. whenever the projection is less than or equal to zero it stays at zero, then continues to accumulate from the next positive movement (Purchase Order received).
You will see from my last post where I currently have it. It is partially correct in that it worked after the first positive entry but as the calculation is based on the original accumulation it falls apart very quickly.
I essentially need to work out how to reset the count when it goes below zero. I’m hoping you can help steer me in the right direction.
If the information here is not enough, I’ll try and put together another sample file and post that in a couple days.
Thanks in advance.
Mark D

[Melissa]

Hi @ZENMarkD,

I’ll try to look into this later this week (if your query remains unresolved).
All the best.

[EnterpriseDNA]

Thank you for looking into this @Melissa

[Melissa]

Hi @ZENMarkD,

Just a quick update, something came up so I didn’t get around to this but I will get back to you.

[ZENMarkD]

No worries at all. Just when you can is fine. Thanks again!

[Melissa]

Hi @ZENMarkD,

Don’t tell anyone, I squeezed it in…
See if this works for you. Just copy the full script into a new blank query.

let
    BufferMovement = List.Buffer( AddIndex[Stock Movement] ),
    BufferRef = List.Buffer( AddIndex[REF] ),
    CalcSOH = List.Generate(
                ()=> [ v = BufferMovement{0}, i = 0 ],
                each [i] < List.Count( BufferMovement ),
                each try if ( BufferRef{[i]} = BufferRef{[i] +1} and  ( [v] + BufferMovement{[i] +1} ) >0 )
                    then [ v = [v] + BufferMovement{[i] +1}, i = [i] +1 ]
                    else if ( BufferRef{[i]} = BufferRef{[i] +1} and ( [v] + BufferMovement{[i] +1}) <0 and BufferMovement{[i] +1} <0 )
                    then [ v = 0, i = [i] +1 ]
                    else [ v = BufferMovement{[i] +1}, i = [i] +1 ]
                    otherwise [ v = BufferMovement{[i] +1}, i = [i] +1 ],
                each [v]
            ),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdlLasMwGEXhrQSPbdC9elgalw6bDALtIGT/26hftHUgDZgzsmzMwRg+foFut851iIOD3fXd+fr1djlfT8Pp4/L5HkJVmp7q94X5EmII89PYSl/qtJpvlPvo7t5PvXasN2yNx14Mx3qlpPXzxhoeimK/UEN6lUu7XNpyOfdtqy3L5feRsUjGEhnLZKyQsZGMVTLWyJgCWkMNCEUgVIFQBkIdCIUgVIJQCkItGLVgdh4ctGCn1GterPd/iqgHox6MejDqwagHox6MeojsbBjyq1jexfIWc+tj3mrret0gobnI5hKby2yusLmRzVU219jctGFie7ALwTAEyxBMQ7ANwTgE6xDMQ7APwz5Mzw3Yh2Efhn0Y9mHYh2Efhn0Y9hFhH5GeH0N5lSu7XNlyMf3s+ZblukkDY5GMJTKWyVghYyMZq2SskbFpU0bWUANCEQhVIJSBUAdCIQiVIJSCUAtGLZidB6gFoxaMWjBqwagFoxaMWjBqIbIW/j0izUuNOyJ93jt2RPq89+SI9P4N", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, REF = _t, #"Month & Year" = _t, MTHnYEAR = _t, #"Stock Movement" = _t, OrderQTYDue = _t, Daily_FCST = _t]),
    ReplValue = Table.ReplaceValue(Source,",",".",Replacer.ReplaceText,{"Stock Movement", "Daily_FCST"}),
    ChType = Table.TransformColumnTypes(ReplValue,{{"Date", type date}, {"Month & Year", type date}, {"MTHnYEAR", Int64.Type}, {"Stock Movement", Currency.Type}, {"Daily_FCST", Currency.Type}, {"OrderQTYDue", Int64.Type}}),
    SortRows = Table.Sort(ChType,{{"REF", Order.Ascending}, {"Date", Order.Ascending}}),
    AddIndex = Table.AddIndexColumn( SortRows, "SOH", 0, 1, Int64.Type),
    GetSOH = Table.ReplaceValue(AddIndex, each [SOH], each CalcSOH{[SOH]},Replacer.ReplaceValue,{"SOH"}),
    ChangedType = Table.TransformColumnTypes(GetSOH,{{"SOH", Currency.Type}})

in
    ChangedType

I’ve sorted on Date and REF before adding an Index column to your table. Then load the Stock Movements and REF’s into memory, so I can check values against each other in the CalcSOH step. That step outputs 1 single list with values, I’ve used the Index column to extract the line value.

With this result.

.
Here’s your sample file:
Sample Projected SOH (1).pbix (148.4 KB)

I hope this is helpful

[jafernandezpuga]

Hello @Melissa,
It’s impressive, hats off.
I was wondering how to solve it but I couldn’t. Very useful the use of the lists in memory and then to combine the result with the table using the index.

Looking at the solution, I got a bit lost when looking at the first 2 steps where it references AddIndex and this one is below. Can AddIndex[Stock Movement] be referenced before the AddIndex = Table.AddIndexColumn( SortRows, “SOH”, 0, 1, Int64.Type), step?

Regards,

[Melissa]

Hi @jafernandezpuga,

Thank you and thank you for the support you provide on this forum!

In regard to your question.
That’s no problem. M code looks sequential because of the way, we as users, built or structure it but that is not a requirement. What is important for the M engine is the dependency chain. If you’d like to learn more about basic concepts in M, I’d recommend the full course this chapter (below) is a part of.

let Expressions, Variables And Identifiers

[EnterpriseDNA]

Hi @Melissa, Thank you for always providing this group with great solutions. We greatly appreciate your efforts and your share of knowledge.

Hello @ZENMarkD, did the response from @Melissa help you with your query?

We just want to check if you still need further help with this post?

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

[ZENMarkD]

Thanks so much @Melissa ! I’m assuming then that in my live file I need to replace the Source table with the actual Stock Movements file?

Currently the Stock Movements table is created with DAX using CROSSJOIN as I didn’t have the M Query know-how to build it that way.

I then calculate the Stock Movement in a very long-winded fashion but it works:

Stock Movement =
VAR MonthlyFCST =
LOOKUPVALUE(
Forecasts[Unit FCST],
Forecasts[REF],
‘Stock Movements’[REF],
Forecasts[Month & Year],
‘Stock Movements’[Month & Year]
)
VAR DaysInMonth = DAY( ENDOFMONTH(‘Stock Movements’[Date] ) )
VAR XFerQTYDue = LOOKUPVALUE( Transfers[QTYONHAND],
Transfers[DUE DATE], ‘Stock Movements’[Date],
Transfers[REF], ‘Stock Movements’[REF] )
VAR XFerQTYSent = IF( MAX( TransfersSent[TRANSFER DATE] ) < TODAY(), 0,
CALCULATE( SUM( TransfersSent[QTYSent] ), TREATAS( VALUES( ‘Stock Movements’[REF] ), TransfersSent[CWRef] ), TREATAS( VALUES(‘Stock Movements’[Date] ), TransfersSent[TRANSFER DATE] ) ))
VAR StockMovement = -ROUND(DIVIDE( MonthlyFCST, DaysInMonth, 0 ), 2) + XFerQTYDue - XFerQTYSent + ‘Stock Movements’[OrderQTYDue]

Return
IF( ‘Stock Movements’[Date] = TODAY(), [Current SOH Units] + StockMovement,
StockMovement )

It then references multiple tables for its data (e.g. Daily FCST from a Forecast table, Orders from an Outstanding Orders table, Item Info from an Item table, etc.) as calculated columns.

With that said, I should be able to recreate it in Power Query instead now by merging queries. I just need to work out how to do the initial Stock Movement calculation using M Query instead of DAX.

Am I on the right track?

[ZENMarkD]

I would appreciate it if you could keep it active for another week or so, as with Easter coming up as well as other priorities, it is going to take some time for me to test it.

I may need a little more help with this. I will know by end of next week if I do indeed need assistance, at which time I’ll respond or mark it as solved.

Thank you.

[Melissa]

@ZENMarkD

I’m assuming then that in my live file I need to replace the Source table with the actual Stock Movements file?

Yes, that is correct Mark. Referencing it in the Source step will do it.

@ZENMarkD

I should be able to recreate it in Power Query instead now by merging queries. I just need to work out how to do the initial Stock Movement calculation using M Query instead of DAX.

If you need any help with that, just create a new topic and make sure to include a data file (xlsx) as well. Members will gladly provide support.

[EnterpriseDNA]

Hi @ZenMarkD, we’ve noticed that no response has been received from you since a few days ago.

We just want to check if you still need further help with this post?

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

[ZENMarkD]

Please see my previous reply to this question when asked a couple days ago. If you need to mark it as solved, fine, but I won’t know if it is solved until I test it, which will be in a week or so, after Easter.

[EnterpriseDNA]

Hello @ZenMarkD, will open this thread for any update on the result of your testing.

Let us know if you have any additional concerns.