Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Inventory calculation using DAX

Hi All,   I want to calculate the Closing/Opening inventory using DAX.    What I have: Brand |Jan Inv a |400 b |750 c |400   Brand |Month |Sales a |Oct-20 |900 a |Nov-20 |500 a |Dec-20 |...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

    I created a sample pbix file(see attachment) for you, please check whether that is what you want.

    Best Regards

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thanks Amit for the reply.

 

Unfortunately this is not giving me the answer.

The Jan Inventory table is not connected with the Calendar table(I missed to mention this ealrlier)

Secondly, the closing calculation is Opening + Month sales - Average of past 3 Month Sales. I didn't find the average calculation in the DAXs.

 

Also you have used a hyphen in the last DAX, not sure about this one. I just replaced with a 0.

 

Unfortunately I won't be able to upload a sample pbix here. If you have created a pbix with the above data, could you please upload here ?

 

Thanks

Kris

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Anonymous , - was -1.

 

But I am not very clear on the structure of the inventory table. Can you confirm you have only first inventory or you have month on column

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Amit,

     

    Inventory table has brand name and it's inventory value, nothing else.

     

    HTH

     

    Kris

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Anonymous ,

      I have a question about the sales value of February in the expected results you listed. According to the data in your table, the sales of Brand a in February is 1000, and the sales of Brand b is 500. Why are you here Brand a and b are 800 and 1000 respectively ? And is the OP Inv of the next month equal to the CI Inv of the previous month? There is also a recursive calculation involved, which may not be easily implemented using DAX...

      Best Regards

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi

         

        "..According to the data in your table, the sales of Brand a in February is 1000, and the sales of Brand b is 500. Why are you here Brand a and b are 800 and 1000 respectively ? "

         

        It's a copy paste mistake 😞

         

        "..And is the OP Inv of the next month equal to the CI Inv of the previous month? "

         

        Yes. I also think it requires a recursive calculation.

         

        Kris