Forum Discussion

KB88's avatar
KB88
Frequent Visitor
2 years ago

Slow Moving Inventory Dax Support

I am still working on my Slow Moving Inventory report. In my last post I was trying to create this based off previous inventory levels, but now I have forecast info. I have 3 tables, Inventory, IBP (Forecast info), and calendar. I am trying to make this table so when I have a month selected it shows me the current month inventory level from Inventory Quantity in the Inventory table. It will also show me the next 6 months of AOP data (Feb-July) in this case. My problem, is when I have added the inventory quantity to the table, it duplicates the materials. Is there a way to get this so only 1 row per material is shown?

 

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    KB88 Sorry, having trouble following, can you post sample data as text and expected output?
    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

    • KB88's avatar
      KB88
      Frequent Visitor
      2 years ago

      Relevant Tables and Relationships:

       

      Inv Table

      Inventory Special Stock TypeInventory Stock TypeMaterialMaterial Base UnitMaterial ClassPlantInventory QuantityEnding Balance Date
       Unrestricted-Use StockDEAOE 25% ISOPENTANE/PT 1635LKGFinished GoodsGLOBE LOGISTICS WAREHOUSE3804.001/5/2024
       Unrestricted-Use StockBEM 20% ISOPENTANE/PT 7500LKGFinished GoodsPASADENA4536.001/5/2024
       Unrestricted-Use StockDEAOE 25% ISOPENTANE/PT 1635LKGFinished GoodsGLOBE LOGISTICS WAREHOUSE3804.001/12/2024
       Unrestricted-Use StockBEM 20% ISOPENTANE/PT 7500LKGFinished GoodsPASADENA4472.001/12/2024
      Orders on HandUnrestricted-Use StockBEM 20% ISOPENTANE/PT 7500LKGFinished GoodsPASADENA4536.001/12/2024
       Unrestricted-Use StockDEAOE 25% ISOPENTANE/PT 1635LKGFinished GoodsGLOBE LOGISTICS WAREHOUSE3804.001/19/2024
       Unrestricted-Use StockBEM 20% ISOPENTANE/PT 7500LKGFinished GoodsPASADENA4472.001/19/2024
      Orders on HandUnrestricted-Use StockBEM 20% ISOPENTANE/PT 7500LKGFinished GoodsPASADENA4536.001/19/2024
       Unrestricted-Use StockDEAOE 25% ISOPENTANE/PT 1635LKGFinished GoodsGLOBE LOGISTICS WAREHOUSE3804.001/26/2024
       Unrestricted-Use StockBEM 20% ISOPENTANE/PT 7500LKGFinished GoodsPASADENA4472.001/26/2024
       Unrestricted-Use StockDEAOE 25% ISOPENTANE/PT 1635LKGFinished GoodsGLOBE LOGISTICS WAREHOUSE3804.002/2/2024
      Orders on HandUnrestricted-Use StockBEM 20% ISOPENTANE/PT 7500LKGFinished GoodsPASADENA9017.002/2/2024
       Unrestricted-Use StockDEAOE 25% ISOPENTANE/PT 1635LKGFinished GoodsGLOBE LOGISTICS WAREHOUSE3804.002/9/2024
       Unrestricted-Use StockBEM 20% ISOPENTANE/PT 7500LKGFinished GoodsPASADENA4472.002/9/2024
      Orders on HandUnrestricted-Use StockBEM 20% ISOPENTANE/PT 7500LKGFinished GoodsPASADENA4472.002/9/2024
       Unrestricted-Use StockDEAOE 25% ISOPENTANE/PT 1635LKGFinished GoodsGLOBE LOGISTICS WAREHOUSE3804.002/16/2024
       Unrestricted-Use StockBEM 20% ISOPENTANE/PT 7500LKGFinished GoodsPASADENA4472.002/16/2024
      Orders on HandUnrestricted-Use StockBEM 20% ISOPENTANE/PT 7500LKGFinished GoodsPASADENA4472.002/16/2024
       Unrestricted-Use StockDEAOE 25% ISOPENTANE/PT 1635LKGFinished GoodsGLOBE LOGISTICS WAREHOUSE3804.002/23/2024
      Orders on HandUnrestricted-Use StockBEM 20% ISOPENTANE/PT 7500LKGFinished GoodsPASADENA4472.002/23/2024
       Unrestricted-Use StockDEAOE 25% ISOPENTANE/PT 1635LKGFinished GoodsGLOBE LOGISTICS WAREHOUSE3804.003/1/2024
       Unrestricted-Use StockBEM 20% ISOPENTANE/PT 7500LKGFinished GoodsPASADENA4300.003/1/2024
      Orders on HandUnrestricted-Use StockBEM 20% ISOPENTANE/PT 7500LKGFinished GoodsPASADENA4472.003/1/2024
       Unrestricted-Use StockDEAOE 25% ISOPENTANE/PT 1635LKGFinished GoodsGLOBE LOGISTICS WAREHOUSE3804.003/8/2024
      Orders on HandUnrestricted-Use StockBEM 20% ISOPENTANE/PT 7500LKGFinished GoodsPASADENA13199.003/8/2024
       Unrestricted-Use StockDEAOE 25% ISOPENTANE/PT 1635LKGFinished GoodsPROCESS DEVELOPMENT CENTER0.003/15/2024
      Orders on HandUnrestricted-Use StockBEM 20% ISOPENTANE/PT 7500LKGFinished GoodsPASADENA13199.003/15/2024
      Orders on HandUnrestricted-Use StockBEM 20% ISOPENTANE/PT 7500LKGFinished GoodsPASADENA8727.003/22/2024
      Orders on HandUnrestricted-Use StockBEM 20% ISOPENTANE/PT 7500LKGFinished GoodsPASADENA8727.003/29/2024
      Orders on HandUnrestricted-Use StockBEM 20% ISOPENTANE/PT 7500LKGFinished GoodsPASADENA4300.004/5/2024
      Orders on HandUnrestricted-Use StockBEM 20% ISOPENTANE/PT 7500LKGFinished GoodsPASADENA4300.004/12/2024
      Stock in TransitBlocked StockBEM 20% ISOPENTANE/PT 7500LKGFinished GoodsPASADENA4300.004/19/2024
       Unrestricted-Use StockDEAOE 25% ISOPENTANE/PT 1635LKGFinished GoodsPROCESS DEVELOPMENT CENTER0.004/26/2024

      IBP Table

      KF_DateProduct_DescriptionProduct_IDAOP Qty
      9/30/2024DEAOE 25% ISOPENTANE,PT 1635L100032401900
      6/30/2024DEAOE 25% ISOPENTANE,PT 1635L100032401900
      11/30/2024DEAOE 25% ISOPENTANE,PT 1635L100032401900
      3/31/2024DEAOE 25% ISOPENTANE,PT 1635L100032401900
      11/30/2024BEM 20% ISOPENTANE,PT 7500L100032439000
      4/30/2024BEM 20% ISOPENTANE,PT 7500L100032439000
      12/31/2024BEM 20% ISOPENTANE,PT 7500L100032434500
      8/31/2024BEM 20% ISOPENTANE,PT 7500L100032439000
      9/30/2024BEM 20% ISOPENTANE,PT 7500L100032439000
      5/31/2024BEM 20% ISOPENTANE,PT 7500L100032439000
      7/31/2024BEM 20% ISOPENTANE,PT 7500L100032439000
      1/31/2024BEM 20% ISOPENTANE,PT 7500L100032439000
      2/29/2024BEM 20% ISOPENTANE,PT 7500L100032434500
      3/31/2024BEM 20% ISOPENTANE,PT 7500L100032439000
      6/30/2024BEM 20% ISOPENTANE,PT 7500L100032439000
      10/31/2024BEM 20% ISOPENTANE,PT 7500L100032439000

      What I would like to Show is a table like this based off a slicer for month (Ex: January Selected). A Slow mover is defined as anything that does not have 2x the current inventory forecasted.

      MaterialSelected Month current inventoryAOP Next 6 Months of InventorySlow Mover
      DEAOE 25% ISOPENTANE,PT 1635L152165700Yes
      BEM 20% ISOPENTANE,PT 7500L2702449500No

       

       

      Thank you in advanced!

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi all,thanks for the quick reply, I'll add more.

        Hi KB88 ,

        Regarding your question, since there is no data for table 'Material Key', 'Calendar', I made assumption.

        I created the relationships between the tables based on your image, but I deleted one of the relationships and it affected the calculation.

        Please follow these steps:

        1.Use the following DAX expression to create a table

        Table = SUMMARIZE('IBP',[Product_Description],[Product_ID])

        2.Use the following DAX expression to create measures

        Selected Month current inventory = 
VAR _ProductID = SELECTEDVALUE('Table'[Product_ID])
VAR _Marterial = MAXX(FILTER('Material Key',[Material Key] = _ProductID),[Material])
RETURN SUMX(FILTER('Inv',[Material] = _Marterial),[Inventory Quantity])
        AOP Next 6 Months of Inventory = 
VAR _Production_Description = SELECTEDVALUE('Table'[Product_Description])
VAR _next_6_month = CALENDAR(MIN('Calendar'[Date]),EOMONTH(EDATE(MIN('Calendar'[Date]),5),0))
RETURN SUMX(FILTER('IBP',[KF_Date] IN _next_6_month && [Product_Description] = _Production_Description),[AOP Qty])
        Slow Mover = IF([AOP Next 6 Months of Inventory] * 2 > [Selected Month current inventory], "NO","YES")

        3.Final output

         

        Best Regards,
        Wenbin Zhou