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 (...
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