cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RNair
Helper I
Helper I

Inventory Management

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 AColumn BColumn CColumn DColumn EColumn FColumn GColumn HColumn IColumn JColumn KColumn L
DateProductTypeQtyTransaction ValueCost Basis for "Buys"Qty before transactionQty After transactionUnit cost Basis before transactionUnit Cost Basis afer transactionCost Basis for "Sells"Cost Basis
1/01/2020ShoeBuy100200200.0000 100 2.000 200.0000
16/01/2020ShoeBuy200420420.00001003002.0002.067 420.0000
31/01/2020ShoeSell-150  3001502.0672.067-310.0000-310.0000
15/02/2020ShoeSell-75  150752.0672.067-155.0000-155.0000
1/03/2020ShoeBuy240468468.0000753152.0671.978 468.0000
16/03/2020ShoeBuy250512.5512.50003155651.9782.010 512.5000
31/03/2020ShoeSell-100  5654652.0102.010-200.9735-200.9735
15/04/2020ShoeSell-100  4653652.0102.010-200.9735-200.9735
30/04/2020ShoeBuy250575575.00003656152.0102.128 575.0000
15/05/2020ShoeBuy350805805.00006159652.1282.190 805.0000
30/05/2020ShoeSell-450  9655152.1902.190-985.5947-985.5947
14/06/2020ShoeBuy250500500.00005157652.1902.128 500.0000
29/06/2020ShoeBuy150277.5277.50007659152.1282.082 277.5000
14/07/2020ShoeSell-95  9158202.0822.082-197.8345-197.8345
6/01/2020UmbrellaBuy32522752,275.0000 325 7.000 2,275.0000
21/01/2020UmbrellaBuy6254562.54,562.50003259507.0007.197 4,562.5000
5/02/2020UmbrellaSell-425  9505257.1977.197-3,058.8816-3,058.8816
20/02/2020UmbrellaSell-200  5253257.1977.197-1,439.4737-1,439.4737
6/03/2020UmbrellaBuy7455103.255,103.250032510707.1976.956 5,103.2500
21/03/2020UmbrellaBuy7755541.255,541.2500107018456.9567.037 5,541.2500
5/04/2020UmbrellaSell-275  184515707.0377.037-1,935.2316-1,935.2316
20/04/2020UmbrellaSell-275  157012957.0377.037-1,935.2316-1,935.2316
5/05/2020UmbrellaBuy7756122.56,122.5000129520707.0377.360 6,122.5000
20/05/2020UmbrellaBuy10758492.58,492.5000207031457.3607.545 8,492.5000
4/06/2020UmbrellaSell-1325  314518207.5457.545-9,996.7696-9,996.7696
19/06/2020UmbrellaBuy77554255,425.0000182025957.5457.382 5,425.0000
4/07/2020UmbrellaBuy4753111.253,111.2500259530707.3827.253 3,111.2500
19/07/2020UmbrellaSell-260  307028107.2537.253-1,885.8606-1,885.8606
1 ACCEPTED SOLUTION
MFelix
Super User III
Super User III

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/


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

14 REPLIES 14
MFelix
Super User III
Super User III

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

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

 

Please advise.

 

Regards,

 

Ramesan

MFelix
Super User III
Super User III

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/


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

mehmet
Frequent Visitor

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

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
Super User III
Super User III

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

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



AlB
Super User III
Super User III

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 

SU18_powerbi_badge

 

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
Super User III
Super User III

@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 

SU18_powerbi_badge

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
Super User III
Super User III

@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 

 

SU18_powerbi_badge

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
Super User III
Super User III

 

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

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

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors