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 (...
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity 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