cancel
Showing results for
Did you mean:
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

1 ACCEPTED SOLUTION
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

14 REPLIES 14
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

Helper I

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

Helper I

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

Regards,

Ramesan

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

Frequent Visitor

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

Helper I

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

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

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

Helper I

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.

Super User III

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

Helper I

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.

Super User III

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

Helper I

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

Super User III

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

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

#### 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.