Inventory Projection

[todebrownie]

Hello Community, how are you?

I'm new to PowerBI and I'm trying to create a inventory projection.

I would like to plot in a weekly basis (every monday) and would use some reports as starting point.

The calculations would be:

For the first week: Current stock on hand, first week consumption and all the shipments to be received that week consolidated on monday.

For the second week and on: Last week inital inventory - last week consumption + shipments received

When I update the file on the next week I would update the data base and the file would start based on that week monday.

 I alredy created Calendar

and imported the initial reports

But I don't know how to continue and get to a plot like this. Could some one please help me? I think that I need to create some mesures accumulating the period. Thanks in advance 🙂

[v-kkf-msft]

Hi @todebrownie ,

Has your problem been solved? 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[todebrownie]

Hi,

I sent you a private msg with my data. Could you please take a look?

[v-kkf-msft]

Hi @todebrownie ,

I'm very sorry, I didn't read the private message before. I clicked on the link you shared, but it indicated that the link had expired. Could you please share it again?

Best Regards,
Winniz

[todebrownie]

Hi V,

I've just sent you again.

Thank you!

[v-kkf-msft]

Hi @todebrownie ,

Has your problem been solved? If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[v-kkf-msft]

Hi @todebrownie ，

Please try the following formula:

Shipments Acm = 
CALCULATE (
    SUM ( Shipments[Qty Invoiced] ),
    FILTER (
        ALL ( CalendarioM ),
        CalendarioM[WEEKNUM] < MAX ( CalendarioM[WEEKNUM] )
    )
)

Fcst Acm = 
CALCULATE (
    SUM ( Forecast[Value] ),
    FILTER (
        ALL ( CalendarioM ),
        CalendarioM[WEEKNUM] < MAX ( CalendarioM[WEEKNUM] )
    )
)

Measure = 
CALCULATE (
    FIRSTNONBLANKVALUE ( CalendarioM[WEEKNUM], [Initial Stock] ),
    ALLSELECTED ( CalendarioM[WEEKNUM] )
) - [Fcst Acm] + [Shipments Acm]

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-kkf-msft]

Hi @todebrownie ,

Not sure about your data structure, I created the following example data based on my understanding.

Then create these measures:

Weekly Forecast = 
IF (
    MAX ( 'Calendar'[WeekDay] ) = 1,
    CALCULATE (
        SUM ( 'Daily Stock'[Consumption] ),
        FILTER (
            ALLSELECTED ( 'Calendar' ),
            'Calendar'[WeekNum] = MAX ( 'Calendar'[WeekNum] )
        )
    )
)

SumShipments = 
IF (
    MAX ( 'Calendar'[WeekDay] ) = 1,
    CALCULATE (
        SUM ( 'Daily Stock'[Shipments] ),
        FILTER (
            ALLSELECTED ( 'Calendar' ),
            'Calendar'[WeekNum] = MAX ( 'Calendar'[WeekNum] )
        )
    )
)

ForecastInventory = 
VAR current_Mo =
    CALCULATE (
        MIN ( 'Daily Stock'[Date] ),
        KEEPFILTERS ( 'Calendar'[WeekDay] = 1 ),
        ALLSELECTED ( 'Calendar' )
    )
VAR stock =
    CALCULATE (
        SUM ( 'Daily Stock'[Inventory] ),
        FILTER ( ALLSELECTED ( 'Calendar' ), 'Calendar'[Date] = current_Mo )
    )
VAR W_S =
    SUMX (
        FILTER (
            ALLSELECTED ( 'Calendar' ),
            'Calendar'[Date] < MAX ( 'Calendar'[Date] )
        ),
        [Weekly Forecast] - [SumShipments]
    )
RETURN
    IF (
        MAX ( 'Calendar'[Date] ) = current_Mo,
        SUM ( 'Daily Stock'[Inventory] ),
        IF (
            MAX ( 'Calendar'[WeekDay] ) = 1
                && MAX ( 'Daily Stock'[Date] ) <> BLANK (),
            stock - W_S
        )
    )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.