Stock level forecast

DAX DAX Calculations
1

Hello,

not the first time I posted some issues with this project. In the past I got help from @AntrikshSharma and some others and hope he (or somebody else) will be able to have one more look to help me out.

This was the previous post about this project: https://forum.enterprisedna.co/t/stock-level-forecast-calculation/10886/24

The measure TP was created and seemed to be working by then. It is now called Total Purchases, but is showing some values which I really don’t understand.
For example, for JJJWW 202108 it is showing -30.199 and I can’t figure out why this is.

For the week after that, 202109 it is giving a correct result. But 202110 is wrong again.

The measure should give the totals for QTY Forecast + QTY Container Shipped + QTY Open purchase + Qty Open. But it must be used in a weekly context (see page weekly overview), but als on product context (page product overview) and show correct Totals at the end of the table.

I have attached a copy of the report. It would be great if somebody can help me out again.
Dummy Model 26-02.pbix (3.0 MB)

2

Hi @marieke

This Total Purchases formula has AllShippedQuantity like constants value for this case 31725 .

image_2021-02-26_101235
image_2021-02-26_101235723×305 53.4 KB

3

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

4

Hi @jbressan

This measure was done like this to make sure the totals will be fine. As 31725 is the total for the QTY container shipped column. But I don’t know how to write this measure so it will give the correct numbers on each row, and in the total for the column.
Because it should not return this value on each row, there is should give the cumulative amount till that week.
And then it should work on a weekly base, but also on product base as on the next sheet…

5

Hi @marieke,

Give this a go, else provide a mock up with expected the results. Thanks.

Total Purchases v2 = 
VAR firstWeek = MINX( ALLSELECTED( Kalender[JJJJWW] ), [JJJJWW] )
VAR thisWeek = MAX( Kalender[JJJJWW] )
VAR TotalShippedQuantity =
    CALCULATE ( [QTY Container Shipped], Kalender[JJJJWW] >= firstWeek && Kalender[JJJJWW] <= thisWeek  )
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 - TotalShippedQuantity )
            )
        RETURN
            Result
    ) + OpenPurchaseOrder
RETURN
    Result

I hope this is helpful.