Forum Discussion

RNair's avatar
RNair
Helper I
5 years ago
Solved

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 , ...
Need Help
MFelix's avatar
MFelix
Super User
5 years ago

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's avatar
    RNair
    Helper I
    5 years ago

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

     

    Please advise.

     

    Regards,

     

    Ramesan

      • RNair's avatar
        RNair
        Helper I
        5 years ago

        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