Forum Discussion

vw_golf_mk3's avatar
vw_golf_mk3
Helper I
9 years ago

Inventory Stock Monitor

Hi all BI'ers,

 

I would appreciate if some one who can help me with my model below on Inventory Stock Monitor.

 

The idea is to use Power BI visual reflecting stock levels. So we have tables below:-

1. General Ledger table (Containing date, product, Qty(+/-), +tive can be stock arrival or positive adjustments. And -tive can be sales or negative adjustments, etc.)

GL

 

 

 

2. Forecast table (Containing a monthly sales forecast by Product by Qty(can be -tive only to inventory), NB Date field is represented with first date of a month. i.e. 1/12/2015.)

Fcast

3. Purchase Order Expected table (containing products yet to be receipted into warehouse. Columns available are: Date, Product, Qty [must be positive to inventory]). You might ask what happen to it after goods being receipted in. The answer is: It will be one of general ledgers in Table 1 as a positive Qty, meanwhile the purchase order disappears from analysis server.

PO

 

 

4. Product table

Product

 

 

5. Date table

 

The aim is to swtich between Actual SOH and Forecast SOH based on parameters in the above Sales, Forecast, Expected POs tables. 

 

Relationship looks like this:

 

Relationship

 

Problematic Measure I created is:

 

Fcast SOH = CALCULATE(SUMX(GL, GL[Quantity] + RELATED(PO[Fcast Demand]) - RELATED(Forecast[Fcast Demand])))

 

The end result should look like something like below.

EndResult

 

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi vw_golf_mk3,

    Based on your description, I create the following measures.

    Actual Qty On Hand = SUM(GL[Quantity])

    SOH = CALCULATE(SUM(GL[Quantity]),FILTER(GL,GL[Quantity]>0))
    Fcast SOH = [SOH]+SUM(PO[PO-Fcast Demand])-SUM(Fcast[Fcast Demand])

    Then I create a Line chart as follows, for more details, please review this attached PBIX file and check if it works as expected. According to the sample data you provided, the Line chart doesn't make sense as the GL table doesn't contain Quantity of June 2016 amd the PO table only contains forcast demand of June 2016.



     




    Thanks,
    Lydia Zhang

    • vw_golf_mk3's avatar
      vw_golf_mk3
      Helper I
      9 years ago

      Thanks for your reply, Lydia. Have not got time to look at your model closely yet.

       

      I think I need to build in a Actual to Month feature to get forecast SOH to the same level of SOH. And for later months, Forecast will still continue. If you drop a filter on product, it will be more clear to look into stock level product by product.

       

      I have adjusted your PBIX model.

       

      Thanks

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi vw_golf_mk3,


        I am not clearly sure about your requirement. Based on your description and screenshot, it seems that you want to forecast SOH for July 2016 and later months. If that is the case, you would need to add data of July 2016 and later months in your data source.


        Thanks,
        Lydia Zhang