Forum Discussion

todebrownie's avatar
todebrownie
Regular Visitor
4 years ago

Inventory Projection

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 🙂

 

Need Help

7 Replies

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    4 years ago

    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.

     

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    4 years ago

    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's avatar
      todebrownie
      Regular Visitor
      4 years ago

      Hi,

       

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

      • v-kkf-msft's avatar
        v-kkf-msft
        Community Support
        4 years ago

        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