topic Re: Slow Moving Inventory Dax Support in DAX Commands and Tips

Slow Moving Inventory Dax Support

KB88 — Fri, 30 Aug 2024 16:15:42 GMT

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?

Re: Slow Moving Inventory Dax Support

lbendlin — Fri, 30 Aug 2024 17:35:57 GMT

Use COALESCE to prioritize one value over the other.

Re: Slow Moving Inventory Dax Support

Greg_Deckler — Fri, 30 Aug 2024 17:42:30 GMT

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.

Re: Slow Moving Inventory Dax Support

KB88 — Fri, 30 Aug 2024 19:07:10 GMT

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!

Re: Slow Moving Inventory Dax Support

Anonymous — Mon, 02 Sep 2024 07:14:39 GMT

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