Slow Moving Inventory Dax Support

[KB88]

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?

[Greg_Deckler]

@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]

Relevant Tables and Relationships:

Inv Table

Inventory Special Stock Type	Inventory Stock Type	Material	Material Base Unit	Material Class	Plant	Inventory Quantity	Ending Balance Date
	Unrestricted-Use Stock	DEAOE 25% ISOPENTANE/PT 1635L	KG	Finished Goods	GLOBE LOGISTICS WAREHOUSE	3804.00	1/5/2024
	Unrestricted-Use Stock	BEM 20% ISOPENTANE/PT 7500L	KG	Finished Goods	PASADENA	4536.00	1/5/2024
	Unrestricted-Use Stock	DEAOE 25% ISOPENTANE/PT 1635L	KG	Finished Goods	GLOBE LOGISTICS WAREHOUSE	3804.00	1/12/2024
	Unrestricted-Use Stock	BEM 20% ISOPENTANE/PT 7500L	KG	Finished Goods	PASADENA	4472.00	1/12/2024
Orders on Hand	Unrestricted-Use Stock	BEM 20% ISOPENTANE/PT 7500L	KG	Finished Goods	PASADENA	4536.00	1/12/2024
	Unrestricted-Use Stock	DEAOE 25% ISOPENTANE/PT 1635L	KG	Finished Goods	GLOBE LOGISTICS WAREHOUSE	3804.00	1/19/2024
	Unrestricted-Use Stock	BEM 20% ISOPENTANE/PT 7500L	KG	Finished Goods	PASADENA	4472.00	1/19/2024
Orders on Hand	Unrestricted-Use Stock	BEM 20% ISOPENTANE/PT 7500L	KG	Finished Goods	PASADENA	4536.00	1/19/2024
	Unrestricted-Use Stock	DEAOE 25% ISOPENTANE/PT 1635L	KG	Finished Goods	GLOBE LOGISTICS WAREHOUSE	3804.00	1/26/2024
	Unrestricted-Use Stock	BEM 20% ISOPENTANE/PT 7500L	KG	Finished Goods	PASADENA	4472.00	1/26/2024
	Unrestricted-Use Stock	DEAOE 25% ISOPENTANE/PT 1635L	KG	Finished Goods	GLOBE LOGISTICS WAREHOUSE	3804.00	2/2/2024
Orders on Hand	Unrestricted-Use Stock	BEM 20% ISOPENTANE/PT 7500L	KG	Finished Goods	PASADENA	9017.00	2/2/2024
	Unrestricted-Use Stock	DEAOE 25% ISOPENTANE/PT 1635L	KG	Finished Goods	GLOBE LOGISTICS WAREHOUSE	3804.00	2/9/2024
	Unrestricted-Use Stock	BEM 20% ISOPENTANE/PT 7500L	KG	Finished Goods	PASADENA	4472.00	2/9/2024
Orders on Hand	Unrestricted-Use Stock	BEM 20% ISOPENTANE/PT 7500L	KG	Finished Goods	PASADENA	4472.00	2/9/2024
	Unrestricted-Use Stock	DEAOE 25% ISOPENTANE/PT 1635L	KG	Finished Goods	GLOBE LOGISTICS WAREHOUSE	3804.00	2/16/2024
	Unrestricted-Use Stock	BEM 20% ISOPENTANE/PT 7500L	KG	Finished Goods	PASADENA	4472.00	2/16/2024
Orders on Hand	Unrestricted-Use Stock	BEM 20% ISOPENTANE/PT 7500L	KG	Finished Goods	PASADENA	4472.00	2/16/2024
	Unrestricted-Use Stock	DEAOE 25% ISOPENTANE/PT 1635L	KG	Finished Goods	GLOBE LOGISTICS WAREHOUSE	3804.00	2/23/2024
Orders on Hand	Unrestricted-Use Stock	BEM 20% ISOPENTANE/PT 7500L	KG	Finished Goods	PASADENA	4472.00	2/23/2024
	Unrestricted-Use Stock	DEAOE 25% ISOPENTANE/PT 1635L	KG	Finished Goods	GLOBE LOGISTICS WAREHOUSE	3804.00	3/1/2024
	Unrestricted-Use Stock	BEM 20% ISOPENTANE/PT 7500L	KG	Finished Goods	PASADENA	4300.00	3/1/2024
Orders on Hand	Unrestricted-Use Stock	BEM 20% ISOPENTANE/PT 7500L	KG	Finished Goods	PASADENA	4472.00	3/1/2024
	Unrestricted-Use Stock	DEAOE 25% ISOPENTANE/PT 1635L	KG	Finished Goods	GLOBE LOGISTICS WAREHOUSE	3804.00	3/8/2024
Orders on Hand	Unrestricted-Use Stock	BEM 20% ISOPENTANE/PT 7500L	KG	Finished Goods	PASADENA	13199.00	3/8/2024
	Unrestricted-Use Stock	DEAOE 25% ISOPENTANE/PT 1635L	KG	Finished Goods	PROCESS DEVELOPMENT CENTER	0.00	3/15/2024
Orders on Hand	Unrestricted-Use Stock	BEM 20% ISOPENTANE/PT 7500L	KG	Finished Goods	PASADENA	13199.00	3/15/2024
Orders on Hand	Unrestricted-Use Stock	BEM 20% ISOPENTANE/PT 7500L	KG	Finished Goods	PASADENA	8727.00	3/22/2024
Orders on Hand	Unrestricted-Use Stock	BEM 20% ISOPENTANE/PT 7500L	KG	Finished Goods	PASADENA	8727.00	3/29/2024
Orders on Hand	Unrestricted-Use Stock	BEM 20% ISOPENTANE/PT 7500L	KG	Finished Goods	PASADENA	4300.00	4/5/2024
Orders on Hand	Unrestricted-Use Stock	BEM 20% ISOPENTANE/PT 7500L	KG	Finished Goods	PASADENA	4300.00	4/12/2024
Stock in Transit	Blocked Stock	BEM 20% ISOPENTANE/PT 7500L	KG	Finished Goods	PASADENA	4300.00	4/19/2024
	Unrestricted-Use Stock	DEAOE 25% ISOPENTANE/PT 1635L	KG	Finished Goods	PROCESS DEVELOPMENT CENTER	0.00	4/26/2024

IBP Table

KF_Date	Product_Description	Product_ID	AOP Qty
9/30/2024	DEAOE 25% ISOPENTANE,PT 1635L	10003240	1900
6/30/2024	DEAOE 25% ISOPENTANE,PT 1635L	10003240	1900
11/30/2024	DEAOE 25% ISOPENTANE,PT 1635L	10003240	1900
3/31/2024	DEAOE 25% ISOPENTANE,PT 1635L	10003240	1900
11/30/2024	BEM 20% ISOPENTANE,PT 7500L	10003243	9000
4/30/2024	BEM 20% ISOPENTANE,PT 7500L	10003243	9000
12/31/2024	BEM 20% ISOPENTANE,PT 7500L	10003243	4500
8/31/2024	BEM 20% ISOPENTANE,PT 7500L	10003243	9000
9/30/2024	BEM 20% ISOPENTANE,PT 7500L	10003243	9000
5/31/2024	BEM 20% ISOPENTANE,PT 7500L	10003243	9000
7/31/2024	BEM 20% ISOPENTANE,PT 7500L	10003243	9000
1/31/2024	BEM 20% ISOPENTANE,PT 7500L	10003243	9000
2/29/2024	BEM 20% ISOPENTANE,PT 7500L	10003243	4500
3/31/2024	BEM 20% ISOPENTANE,PT 7500L	10003243	9000
6/30/2024	BEM 20% ISOPENTANE,PT 7500L	10003243	9000
10/31/2024	BEM 20% ISOPENTANE,PT 7500L	10003243	9000

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.

Material	Selected Month current inventory	AOP Next 6 Months of Inventory	Slow Mover
DEAOE 25% ISOPENTANE,PT 1635L	15216	5700	Yes
BEM 20% ISOPENTANE,PT 7500L	27024	49500	No

Thank you in advanced!

[Anonymous]

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

[lbendlin]

Use COALESCE to prioritize one value over the other.