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