Inventory Management

[RNair]

Trying to manage inventory and monitor value of inventory.

You build inventory by buying products. When you sell the inventory value drops.

If you buy 100 units of a product at 200 dollars , say, then the cost basis is 200 dollars. Each item in inventory is valued at 2 dollars.

If you further buy 200 units of the same product at 420 dollars, you now have a total of 300 units at total cost of 620 dollars so each item in inventory is valued at 620/300 =2.067 dollars.

If you sell now, and drop inventory, the drop in value is estimated at 2.067 per unit , which is the average cost of retained inventory.

In following table, columns  A through E are raw data. Columns F G H are simple calculations which are relatively easy.

Column I and Column J are tricky and keep producing circular reference error.

Need help in replicating Columns I and J in PowerBi using measures.

Regards,

RNair

Columns A to E are raw data			Columns F onward are calculated based on the raw data
Column A	Column B	Column C	Column D	Column E	Column F	Column G	Column H	Column I	Column J	Column K	Column L
Date	Product	Type	Qty	Transaction Value	Cost Basis for "Buys"	Qty before transaction	Qty After transaction	Unit cost Basis before transaction	Unit Cost Basis afer transaction	Cost Basis for "Sells"	Cost Basis
1/01/2020	Shoe	Buy	100	200	200.0000		100		2.000		200.0000
16/01/2020	Shoe	Buy	200	420	420.0000	100	300	2.000	2.067		420.0000
31/01/2020	Shoe	Sell	-150			300	150	2.067	2.067	-310.0000	-310.0000
15/02/2020	Shoe	Sell	-75			150	75	2.067	2.067	-155.0000	-155.0000
1/03/2020	Shoe	Buy	240	468	468.0000	75	315	2.067	1.978		468.0000
16/03/2020	Shoe	Buy	250	512.5	512.5000	315	565	1.978	2.010		512.5000
31/03/2020	Shoe	Sell	-100			565	465	2.010	2.010	-200.9735	-200.9735
15/04/2020	Shoe	Sell	-100			465	365	2.010	2.010	-200.9735	-200.9735
30/04/2020	Shoe	Buy	250	575	575.0000	365	615	2.010	2.128		575.0000
15/05/2020	Shoe	Buy	350	805	805.0000	615	965	2.128	2.190		805.0000
30/05/2020	Shoe	Sell	-450			965	515	2.190	2.190	-985.5947	-985.5947
14/06/2020	Shoe	Buy	250	500	500.0000	515	765	2.190	2.128		500.0000
29/06/2020	Shoe	Buy	150	277.5	277.5000	765	915	2.128	2.082		277.5000
14/07/2020	Shoe	Sell	-95			915	820	2.082	2.082	-197.8345	-197.8345
6/01/2020	Umbrella	Buy	325	2275	2,275.0000		325		7.000		2,275.0000
21/01/2020	Umbrella	Buy	625	4562.5	4,562.5000	325	950	7.000	7.197		4,562.5000
5/02/2020	Umbrella	Sell	-425			950	525	7.197	7.197	-3,058.8816	-3,058.8816
20/02/2020	Umbrella	Sell	-200			525	325	7.197	7.197	-1,439.4737	-1,439.4737
6/03/2020	Umbrella	Buy	745	5103.25	5,103.2500	325	1070	7.197	6.956		5,103.2500
21/03/2020	Umbrella	Buy	775	5541.25	5,541.2500	1070	1845	6.956	7.037		5,541.2500
5/04/2020	Umbrella	Sell	-275			1845	1570	7.037	7.037	-1,935.2316	-1,935.2316
20/04/2020	Umbrella	Sell	-275			1570	1295	7.037	7.037	-1,935.2316	-1,935.2316
5/05/2020	Umbrella	Buy	775	6122.5	6,122.5000	1295	2070	7.037	7.360		6,122.5000
20/05/2020	Umbrella	Buy	1075	8492.5	8,492.5000	2070	3145	7.360	7.545		8,492.5000
4/06/2020	Umbrella	Sell	-1325			3145	1820	7.545	7.545	-9,996.7696	-9,996.7696
19/06/2020	Umbrella	Buy	775	5425	5,425.0000	1820	2595	7.545	7.382		5,425.0000
4/07/2020	Umbrella	Buy	475	3111.25	3,111.2500	2595	3070	7.382	7.253		3,111.2500
19/07/2020	Umbrella	Sell	-260			3070	2810	7.253	7.253	-1,885.8606	-1,885.8606

[MFelix]

Hi @RNair ,

I was looking at your data and only getting issue in understanding the calculation when you buy values if I look at the information you place how do you calculate the values for the lines below the two values in BOLD (they are the same values) the cost basis for sells then I assume comes from the previous columns quantity * Unit cost after transaction.

---

@RNair wrote:

Date	Product	Type	Qty	Transaction Value	Cost Basis for "Buys"	Qty before transaction	Qty After transaction	Unit cost Basis before transaction	Unit Cost Basis afer transaction	Cost Basis for "Sells"	Cost Basis
1/01/2020	Shoe	Buy	100	200	200.0000		100		2.000		200.0000
16/01/2020	Shoe	Buy	200	420	420.0000	100	300	2.000	2.067		420.0000
31/01/2020	Shoe	Sell	-150			300	150	2.067	2.067	-310.0000	-310.0000
15/02/2020	Shoe	Sell	-75			150	75	2.067	2.067	-155.0000	-155.0000
1/03/2020	Shoe	Buy	240	468	468.0000	75	315	2.067	1.978		468.0000
16/03/2020	Shoe	Buy	250	512.5	512.5000	315	565	1.978	2.010		512.5000

---

This type of calculations you need to use a filtering based on the current row date and then make cumulatives so if you pick up the correct values for each cumulative it should all come together.

If you can share those two calculations I can give you the needed measures. 

Can you share the calculation in EXCEL type formula I believe I was abble to replicate but not really sure if my tough is correct.

[RNair]

https://1drv.ms/x/s!Aqc2e7y3QZN5mBgYTvraj_c6q_k4?e=ZC04NP

Felix,

Above spreadsheet Sheet1 contains the calculations.

Thank you for looking at my issue.

Regards,

Ramesan

[RNair]

Am I correct in saying that because this is a recursive calculation it cannot be done in DAX?.

Please advise.

Regards,

Ramesan

[MFelix · Accepted solution]

Hi @RNair ,

Sorry for the late reply, yes you are correct recursive calculation cannot be done in DAX, you can simulate it but no exaclty a recursive.

You can do it in Query editor using a formula.

https://www.poweredsolutions.co/2019/07/01/recursive-functions-in-power-bi-power-query/

[mehmet]

Hi, i have same problem. But how can do from quary editor? Could you please help

[RNair]

Miguel,

Thank you for these responses.

I am coming up with a mathematical model that will not require any recursive calculations, but will still get me to the column L.

Once I have this spreadsheeted, and if I need help with the DAX implementation, I will put this up as a separate topic.

Even though I did not fully achieve what I wanted, I learnt a lot through this process.

Regards and Thanks again,

Ramesan Nair

[MFelix]

Hi @RNair, If you need assistance tag me on the post.

[AlB]

Hi @RNair 

By requesting a measure you mean you will use that measure in a table visual with columns A to E in the rows??

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[RNair]

Yes will be displaying these as tables and other visuals also. The objective is to get to column L. I am creating an interface where products and dates are being filtered by slicers so cannot use calculated columns and tables.

[AlB]

@RNair 

1. The objective is column L or columns I and J as you stated earlier? 

2. Can you elaborate on how you get to the 1.978 for "Unit cost Basis before transaction" on the sixth row of the table you show?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[RNair]

1. Column K is just multiplying Column J x Column D for the rows marked "Sell" in Column C. Column L is just addition of Column F and Column K. So yes the ultimate objective is Column L but my difficulty is in calculating Columns I and J.
2. For sell transactions, the unit cost after transaction is the same as before transaction. That means where Column C= Sell; Column J=  Column I. For buy transactions, ie where Column C= "buy", Unit Cost Basis after transaction = ( Unit Cost before transaction x Qty before transaction + Cost of transaction )/ Qty after transaction. ie where Column C = "Buy", (Column I x Column G + Column D)/ Column H. For the row 6;   ( 2.067 x 75 + 468) / 315 = 1.978. 
3. For each row Column I = Column J on the previous row.

Thanks again for the interest.

[AlB]

@RNair 

I see what you mean. That's going to be quite tough. There's recursion there and DAX doesn't lend itself well to that. I'll have a closer look later but I doubt it can be done in the way you propose.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[RNair]

AIB,

Thank you the response and the advice.

I am a little bit disappointed such an incredible software has got an inherent weakness like this.

Have spent almost a couple of months studying DAX now, and am really impressed with its abilities but this has rocked my faith a bit.

 Please spend a little time when able to look at this and advise if any ideas possible.

Thank you again and good night.

Regards,

RNair

[AlB]

Let's see if any of these knowledgeable people have ideas:

@Zubair_Muhammad ,   @mahoneypat , @MFelix ,  @MattAllington , @Greg_Deckler @GilbertQ