Stock level forecast calculation

Hi,

I still tried a lot of things in the meantime as I was hoping I could sort it out myselve, so please find attached a newer version of the PBX file. But there still is a problem in week 49. The formula that works best is your “stock projection-2”. Only for week 49 this is missing 8329 of incoming goods, while for all other weeks with incoming goods, the calculation is correct.

Now I have found out that this number 8329 is coming from another table as the other incoming goods , as there is a difference between goods shipped yet, and goods not shipped yet. This is calculated in measure “QTY container Shipped 2” and this measure itselves seems to be working fine on weekly rows, but in the total below this also misses an amount of 8329, and so the same for 'Total purchases" as this is based on that previous calculation.

It would really be great if you could help me out with this!

thank you, Marieke

Dummy Data 4.pbix (3.1 MB)

Hi @hafizsultan ,

Do you think you would be able to help me out with this issue? No problem if it will take doen time offcourse, but otherwise i’ll have to find another solution to get it sorted…

Or perhaps @Melissa would be able to help me? Or anyone else on this forum?

All help would be very much appreciated.

Many thanks,

Kind regards Marieke

Hi @marieke,

Sorry for responding late. I did try to solve it once, but couldn’t crack it and I wanted to involve some experts to look into it. Basically I wanted to write details of this, so these guys quickly understand our problem.

@Melissa
@BrianJ
@AntrikshSharma

If you guys can step in and help to find issue. Here Stock Projection-2 is a sort of running total to stock
change measure. However, it does not add up Week 49(JJJJWW 202049) value. We are unable to find solution of this problem. Can you please have a look?

Kind Regards,
Hafiz

1 Like

@marieke Is this the desired result?:

Antriksh = 
VAR TempTable =
    -- Pre-Populate Stock change on all Week nums
    ADDCOLUMNS (
        ALLSELECTED ( Kalender[JJJJWW] ),
        "@Stock Change", [Stock Change]
    )
VAR FilterOutFutureWeeks =
    -- Filter rows that are less than equal to current WW
    FILTER (
        TempTable,
        Kalender[JJJJWW] <= MAX ( Kalender[JJJJWW] )
    )
VAR Result =
    IF (
        [Stock Change] <> 0
            && HASONEVALUE ( Kalender[JJJJWW] ),
        SUMX ( FilterOutFutureWeeks, [@Stock Change] )
    )
RETURN
    Result
1 Like

Hi @AntrikshSharma,

many thanks for your help. This is really very helpfull. I already had a measure (thanks to @hafizsultan) to get the last know stock value, so that one, together with your measure, will give me the result I want so that is really great.

There is only one thing that I don’t understand. It is sorted now in another way around so that is great, but I just want to understand what is wrong with it. :slight_smile:
Why is the Total Purchases column giving me an incorrect number? It is missing the amount of 8329. That was also the problem in the measures that @hafizsultan made for me. Do you have any idea what is wrong with it or what will be causing this difference?

and offcourse @hafizsultan many many thanks for helping me, and for contacting the other experts to have a look at it. :blush:

Hi @AntrikshSharma,

Thank you for providing simple and elegant solution. I will try to compare with my solution and see why it was not working.

Hi @marieke,

I am glad that you finally got it working. Regarding your above question, I know why you are getting 30,036 in total. Actually we have created a logic that if shipped quantity is 0, then we do [QTY open] - [Shipped quantity]. Now in total, shipped quantity is not 0, so it will only bring shipped quantity. It is because of below function:

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 () )
    )

Kind Regards,
Hafiz

@marieke I Lack creativity when it comes to naming measures, so ignore that :smiley:

TP =
VAR AllShippedQuantity =
    CALCULATE ( [QTY Container Shipped], ALLSELECTED ( 'Kalender' ) )
VAR OpenPurchaseOrder = [QTY Open Purchase Order]
VAR Result =
    SUMX (
        VALUES ( Kalender[JJJJWW] ),
        VAR ShippedQuantity = [QTY Container Shipped]
        VAR OpenQuantity = [QTY Open]
        VAR Result =
            IF (
                ShippedQuantity <> 0,
                ShippedQuantity,
                IF ( OpenQuantity <> 0, OpenQuantity - AllShippedQuantity )
            )
        RETURN
            Result
    ) + OpenPurchaseOrder
RETURN
    Result

Dummy Data 4 (1).pbix (3.1 MB)

4 Likes

Hi @AntrikshSharma,

this is great, exactly want I wanted.

Many thanks for your help, much appreciated!

Best regards, Marieke

2 Likes

@marieke, @Melissa, @hafizsultan and @AntrikshSharma,

I’ve been following this thread since it started, and I just want to offer kudos to everyone involved in getting to a great solution on a complex problem. This is really the forum working at its absolute best, IMO.

@marieke - thank you for providing a clear explanation of the problem, multiple PBIX files and detailed feedback on what did and didn’t work with regard to the proposed solutions. Those things all make providing quality support so much easier. Also, incredible teamwork by @Melissa getting the initial solution rolling, @hafizsultan carrying the ball a long way down the field and @AntrikshSharma bring it over the goal line. Great creative thinking and interactions throughout - well done, all! :clap: :clap:

  • Brian
5 Likes