Calculating a warehouse items that doesn't move for more than 10 years

[freevission2019]

Hi Community friends,

Good day.

I have a data for a warehouse which contains a lot of items ( commodity codes ), for each item there is a column for the [ on hand Quantity ] and the [ last issue date ] and other columns.

I want to know how many items that do not move ( have not been issued ) for mor than 10 Years ? and i would like to know summation of the related quantities.

I appreciate if someone helps.

Regards.

[Nathaniel_C]

Hi @freevission2019 ,
You will need to give us more information to work on.

Please read this post to get your question answered more quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Nathaniel

[freevission2019]

Thanks a lot Nathaniel for your reply.

I really thought that the information that I provided was enough 😞

I am reading the above mentioned post, and I will provide more info.

Regards.

[freevission2019]

Hi,

This is a snapshot from my data, I want to know how many ( rows ) that their [ Plant Last Issue Dt ] was since more than 10 years.

I hope this information is suffiecient to get answers.

Best Regards

[Nathaniel_C]

Hi @freevission2019 ,
So this is a start, thank you.
Please give us what the expected outcome will be for this snapshot. How do we know how much has been there for 10 years?
Also better than a picture, is to copy and paste, so we can copy and paste into Power BI.

Thanks,

Nathaniel

[freevission2019]

Hi @Nathaniel_C ,

Thanks for your interest and reply.

This data is the actual data from our inventory ( spare parts for maintenance ) . Each row contains the material code ( 9 digits ), Description, unit, Division, Created On, material type, PLANT LAST DATE, ON HAND QUANTITY and ON HAND VALUE.

Some materials have not been issued ( withdrawn ) for long time. we are charged a lot because of these non moving materials. The coulomn No. 8 ( Plant Last Issue Dt ) shows the last transaction's date for each code. For example, the first item is 100000042 , its last issue date was 21/5/2005 means before around 15 years. No one has withdrawn this material for maintenance purpose since 21/5/2005. I am targeting the similar situations in which the [ Plant Last Issue Dt ] is before more than 10 years from now.

Hopefully I could explain the idea well.

Regards.

Material1	Disc.	Base Unit	Division	Created on	Storage bin	Material Type	Plant Last Issue Dt	Area	On Hand Quantity	On Hand Value
100000042	BREAKER,CRCT,GAS,3 P,#FG22000A	Each	RR	21/05/2005	W2R-AD-07A	ZCAP	21/05/2005	COA	1	0.00
100000091	DESICCANT:DRYING,SILICA GEL,GRANULAR,(3)	Each	RR	04/05/2005	A2S-AB-03A	ZCAP	15/10/2018	COA	17	0.00
100000093	COIL,ELEC,CLOSING AND TRIPPING,1020 W,#4	Each	DM	04/05/2005	W1R-AB-06A	ZCAP	04/05/2005	COA	14	0.00
100000153	BREAKER,CRCT,MC,600V,2 POLE,10 A,#SA3210	Each	RR	10/05/2005	M1S-AC-05E	ZCAP	10/05/2005	COA	1	0.00
100000183	SWITCH,PRES,1P2T,28 MM FLANGE,GP,#147181	Each	RR	07/05/2005	W1R-AB-41A	ZCAP	07/05/2005	COA	2	0.00
100000310	BUSHING,ELECTRICAL CONDUCTOR,POWER TRANS	Each	RR	31/05/2005	#	ZCAP	31/05/2005	COA	21	0.00
100000432	ADHESIVE:BONDING,PLIOBOND 20,CAN	Each	IS	25/06/2005	A1S-AF-02F	ZSTK	25/06/2005	COA	1	0.04
100000432	ADHESIVE:BONDING,PLIOBOND 20,CAN	Each	IS	25/06/2005	A2S-AB-01D	ZSTK	25/06/2005	COA	2	0.08
100000509	BREAKER,CRCT,MC,415 VAC/440 VDC,2 POLE	Each	RR	23/07/2005	#	ZCAP	09/07/2017	COA	5	0.00
100000646	BUSHING,ELECTRICAL CONDUCTOR,CEI EN 6013	Each	RR	28/06/2010	Y5B-AB-06A	ZSTK	28/06/2010	COA	1	1.00
100000908	DEVICE: NO MODIFIER	Each	RR	20/05/2006	WH-AA-06A	ZSTK	20/05/2006	COA	36	34,958.83
100000955	ADAPTER,TUBE,GAUGE,GAS INSULATED SWITCHG	Each	RR	16/01/2013	S1B-AA-02A	ZSTK	11/04/2018	COA	3	367.91
100000967	NUT,HEX,STL,M12 DIA,1.75 MM,10 MM HT,#6K	Each	RR	22/05/2006	WH-DD-01B	ZCAP	22/05/2006	COA	72	0.00
100000968	WASHER,FLAT,RD,STL,13 MM ID,2.5 MM TH,#N	Each	RR	22/05/2006	WH-DD-01B	ZCAP	22/05/2006	COA	98	0.00
100001002	CHANGER,TAP,132KV VOLT,#IZSE5492106REV3	Each	RR	10/04/2005	S1B-AA-02B	ZCAP	25/12/2013	COA	1	0.00
100001014	TFMR,PWR,30/50 MVA KVA,3 P,132KV,33KV,#T	Each	RR	20/04/2005	O/01 7 11	ZSTK	20/04/2005	COA	2	2.00

[Nathaniel_C · Accepted solution]

Hi @freevission2019 
Thank you for the explanation. Try this:

Countrows Measure = 
VAR _tenYears =
    TODAY () - 10 * 365.25
VAR _count =
    COUNTROWS ( FILTER ( myTable, myTable[Plant Last Issue Dt] < _tenYears ) )
RETURN
    _count

 My table is called myTable.  (All dates in my picture may not match exactly but the years are correct.) 

Sum On Hand =
VAR _tenYears =
    TODAY () - 10 * 365.25
VAR _calc =
    SUMX (
        FILTER ( myTable, myTable[Plant Last Issue Dt] < _tenYears ),
        myTable[On Hand Quantity]
    )
RETURN
    _calc

 
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[freevission2019]

Hi @Nathaniel_C 

Thanks for your effort.

I liked the measure that you have created and I applied it, but unfortunately it did not give the expected results. I tried also COUNTX and it gave the same result as yours. 

can you examine the measure based on the data that I provided in the previous post.

Regards

[freevission2019]

Hi @Nathaniel_C ,

I examined the measure on the data above and I found it PERFECT 🙂 , but when I applied the same on the whole data it gave strange result. what do you think the problem is?

[Nathaniel_C]

Hello @freevission2019 ,
Without more information, I cannot guess, can you share your pbix?
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel