Stock level forecast calculation

Hi all,

I’m currently working on a forecast for stock levels, based on actual stocklevel, minus sales forecast, plus goods that are planned to arrive. But there needs to be done some calculations and I’m really getting stuck in it now…

Actual stock levels are known per week out of the management system from our company.

There is a sales forecast for every week of the year. We also know when new goods will arrive at the warehouse. So I need to calculate current stock level for this week, minus sales forecast for this week, plus incoming goods.

But the problems is that for future weeks, as in the example from week 202040 onwards, I don’t know the stocklevels yet. This needs to be calculated from the last know actual figure, but i really don’t know how to arrange this.

Please find attached an example of what I have made so far. So stock level for week 2020-39 is based on the last know QTY stock for week 38 (3854) - Sales (38) + incoming goods (2780). that makes a total of 6595. And this total at the end of week 39, should be the start amount for week 40.

This is how i have calculated now so far:

Stock Level = [QTY Stock last week] - [QTY Forecast] + [QTY Container] + [QTY Not Shipped] + [QTY Open Pur]

QTY Stock last week = CALCULATE([QTY Stock end of week week], DATEADD(Kalender[Datum],-7,DAY))

QTY Stock end of week week = CALCULATE(SUM(‘Stockmovement history’[qty_free] ), LASTDATE(Kalender[Datum]))

Perhaps I should use the StockLevel as starting value for the next row? But I tried several ways and all of them are not working, so I hope somebody can give me some advice for this.

Many thanks for your help!

Kind regards, Marieke

Hi @marieke,

Please see if this topic is helpful to you.

Forecast / Project forward my stock inventory value

Hi @marieke, did the response provided by the contributors help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

Hi,

i think this might be the solution for my case too, i will try to work it out somewhere in the next coming days and let you know.

many thanks!

Hi Melissa,

i tried to work some things out based on the topic you advised me and I feel I’m a little bit closer now, but still can’t get it working as it should :frowning:

I have now added a copy of the data model and the report and hope you will be able to help me out.

The stock level for last week is monitored in our system and will be updated every week. The last known value of this stocklevel, needs to be the starting point for future calculations. In this calculations the expected sales forecast, and the planning of incoming goods will affect the stocklevel.

So now in week 202040 we started with a stocklevel of 3694. Expected forecast is -53 to sell and there are no goods coming in this week. So expected outcome at the end of this week should be 3694 - 53 so 3641 and not 4031 as it is telling me now.

And then for week 202041, 3641 should be the starting point, and then forecast of -38 to sell and 800 goods to come in, so a total change of 762. So at the end of week 202041, I expected to see 2879.

Dummy Data.pbix (1.5 MB)

I would really appreciate if somebody can have a look at my file and would be able to help me out.

Many thanks in advance!

Hi @marieke,

Thanks for sharing that.
I’ll look into this tomorrow and get back to you.

Hi @marieke,

I hope to fill in and try to find solution when @Melissa is busy. I quickly tried to create logic and it seems that it is working :). Please find below solution.

Stock Projection-2 =
VAR LastStockDay =
    CALCULATE (
        MAX ( 'Kalender'[JJJJWW] ),
        FILTER (
            ALLSELECTED ( 'Kalender'[JJJJWW] ),
            ISBLANK ( [QTY Stock last week] ) = FALSE ()
        )
    )
VAR ExistingStocks = [QTY Stock last week] + [Stock Change]
VAR ExistingStocksLastWeek =
    CALCULATE ( [QTY Stock last week], 'Kalender'[JJJJWW] = LastStockDay )
        + CALCULATE ( [Stock Change], 'Kalender'[JJJJWW] = LastStockDay )
VAR StockChangeNew =
    CALCULATE (
        [Stock Change],
        FILTER (
            ALLSELECTED ( 'Kalender'[JJJJWW] ),
            'Kalender'[JJJJWW] > LastStockDay
                && 'Kalender'[JJJJWW] <= MAX ( 'Kalender'[JJJJWW] )
        )
    )
VAR FinalMeasure =
    IF (
        ISBLANK ( [QTY Stock last week] ) = TRUE (),
        ExistingStocksLastWeek + StockChangeNew,
        ExistingStocks
    )
RETURN
    FinalMeasure

EDNA_StockInventory_Dummy Data (1).pbix (1.5 MB)

2 Likes

Hello @hafizsultan

this is exactly what I need, many many thanks for your help, it is really much appreciated :slight_smile:

Kind regards, Marieke

1 Like

Hi @marieke,

Glad to hear that it worked for you and I enjoyed working on this problem. Have a nice weekend.

Regards,
Hafiz

Hi @hafizsultan,

can I please ask for your help again with this report? It looked like everything was fine after your help, but now new shipments are on it’s way and I’m facing another issue. I tried several calculations yet, but can’t find the solution…

One order (no. 825) is now partly shipped. The total order is 27.503 SKU, but only 19.755 are shipped now and they will arrive on the 4th of November (we want to use arrival date of a container once a container is planned). So, 7.748 are not shipped yet, and for this we want to use the planned date in the order, so in this case 30-11-20.

Looking at the table in “Check Shipped and Planned” everything seems to be fine.
But then I want to use these figures in a weekly planning as on page “forecast berekening”, and there it goes wrong… In week 45 the shipped 19755 will arrive, but the calculation here also gives me 19.755 as not shipped yet. So in total, it will give me a calculation of 0. And then in week 202049 we expect only the last 7748 SKU to arrive, but then it is giving me the total number for the complete order…

Want i want to see: in week 45 arrival of the 19.755 goods shipped yet, and in week 49 arrival of the planned 7.748…

Can you please help me once again as this report is driving me crazy now :slight_smile:

Dummy Data 2.pbix (3.1 MB)

Hi @marieke,

No problem, this forum is very helpful and you most definitely find solution when you post over here with some good explanation and sample data.

Now, looking at your problem, it is clear what is wrong here. Let me explain problem first and then we can move to the solution.

Your ‘Checked Shipped and planned table’ is working because you are not using anything from you Calendar table. In highlighted figure above, you have [QTY shipped container] as 19755 as you have relation and in this week you have some shipment. But your other measure [QTY not shipped yet] = [QTY Open] - [QTY Container Shipped] is dependent of [QTY Open] and [QTY open Measure] is below:

QTY Open = SUM(‘WH Container Item (Open orders)’[qty_open])

Above table 'WH Container Item (Open orders) has all dates for the end of the month, so it does not match with week 202045 and gives 0 and vice versa.

Now, in order to resolve this issue, I have created 2 measures which will work within this context.

QTY Container Shipped-2 = 
VAR shipped_Qty = [QTY Container Shipped]
VAR QTY_open = [QTY Open]
VAR all_shipped_qty =
    CALCULATE ( [QTY Container Shipped], ALLSELECTED ( 'Kalender' ) )
RETURN
    IF (
        shipped_Qty <> 0,
        shipped_Qty,
        IF ( QTY_open <> 0, QTY_open - all_shipped_qty, BLANK () )
    )

QTY Not Shipped Yet-2 = 
// [QTY Open] -  [QTY Container Shipped-2]
VAR QTY_Open_ALL =
    CALCULATE ( [QTY Open], ALLSELECTED ( 'Kalender' ) )
VAR QTY_Shipped_ruhning_total =
    CALCULATE (
        [QTY Container Shipped-2],
        FILTER ( 'Kalender', 'Kalender'[Datum] <= MAX ( 'Kalender'[Datum] ) )
    )
RETURN
    IF (
        [QTY Container Shipped-2] <> 0,
        QTY_Open_ALL - QTY_Shipped_ruhning_total,
        BLANK ()
    )

Below is the result:

We might need to do some work in the total if it is required. Please feel free to get in touch in case of any confusion as it took me some time to understand data model and your calculations :slight_smile:

EDNA_Shipment_Dummy Data 2.pbix (3.0 MB)

2 Likes

Hi @hafizsultan

Many thanks for your help again, I will look at it into more detail later this week and let you know if it helped me out, but I think it certainly will do :slight_smile:
Thank you!

1 Like

Hi @hafizsultan,

I have now used used your proposed formules in my report, and almost everything is fine now, except one thing:

I week 45 the formula for the total stock level (voorraadniveau) is working fine, but for week 49 it doesn’t calculate the incoming goods, while the formulas are the same for every week…

Do you have any idea how this can be sorted out?

Hi @marieke,

Can you please send me pbix file with same table and values? I want to replicate and see that problem.

Regards,
Hafiz

1 Like

Hi @hafizsultan,

please find attached the pbix file, i hope you can help me sorting this out :slight_smile:

Dummy Data 3.pbix (3.1 MB)

Hi @hafizsultan,

In the meantime I’m still trying to sort this out, but I can’t get in working properly unfortunatelly.

But, now I have presented the report it in a different lay-out, as I was requested to do so by my manager. It will give him a more detailed overview of all products and their stock levels. I have only changed the lay-out, not the formulas. So the problem still exists.

But the strange thing is, I have found out that is works correctly on individually product lines.
As for example, looking at product 64428NP, Voorraad (= Stock Level) for week 20248 was 5773. Then in week 202049 57 will be sold and 1750 will arrive, so a change of 1693. That will make a stock level of 5773 + 1693 = 7466 and that is exactly what the report is displaying.

For the other product in this printscreen, goods already arrived earlier in week 202045 (where the complete calculation is working fine) so stock level will only change with -61 sold.

Looking at the total top rows, the individually rows are all correct, but in the last total row, it should give the total stocklevel of 7466 Plus 2498, (9964 )but in only shows 8214 instead :frowning:

It really would be great if you would have some time to look at it and hopefully can figure out what the problem is…

Dummy Data 3.pbix (3.1 MB)

Hi @marieke,

Thanks for your detailed description. Actually it was not working because in total, we did not had any filter context. Now, I created filter context that you used in the the table to calculate total and it seems working. Please try to create it and let me know if it does not work.

Stock proj-3 =
VAR newtotal =
    SUMX (
        SUMMARIZE (
            'Producten Uitgebreid',
            'Producten Uitgebreid'[sku],
            'Producten Uitgebreid'[Leverancier],
            "Stock Projection", [Stock Projection-2]
        ),
        [Stock Projection]
    )
RETURN
    IF (
        HASONEVALUE ( 'Producten Uitgebreid'[sku] ),
        [Stock Projection-2],
        newtotal
    )

1 Like

Hello, @hafizsultan,

Many thanks again!

It looks fine now, but just for my own understanding, the previous method already seemed to work in week 45, but not in week 49. If this only has to do with the total calculation, then why was it already working in week 45?

Hi @marieke,

That’s great that it is working for you. To be honest, I am not sure about it as it could be due to some intermediate calculations as we are using different measures to come up with the total or it could be issue of some model relationship.

But you are welcome to come back if you feel any issue with calculations as I can surely miss certain things as I don’t understand the language used(may be Dutch/Europen) :slight_smile:

Regards,
Hafiz