Forum Discussion

mail2vjj's avatar
mail2vjj
Helper III
8 years ago
Solved

Closing Stock Calculation

Hello, I have the following 2 tables. First one is Inward and second one is Outward.   Inward       Date Size Type Quantity 01-01-18 10x10 A 100 01-01-18 20x20 B 100 01-...
Need Help
Show and Tell
Tips and Tricks
  • mail2vjj's avatar
    mail2vjj
    8 years ago

    Zubair_Muhammad

     

    Thanks again for taking the time to help me out.

     

    I have updated my file to get the closing stock after taking into consideration the last date for every month, all thanks to your help. I have created a new measure 'On Hand Quantity' in the 'Final Table'.

    Here is the link:

    https://1drv.ms/f/s!Ap0qSKP-4qpThCGX0VuaSk-I9cxx

     

    Now I am trying to get the prices in my closing stock table, so that all my closing stock can be bifurcated.

     

    I would really like to know from where have you learned how to code DAX cause you are so quick with your solutions and they work!

     

    I have been spending hours an hours and not getting any results. If you could please tell me if there is any book that I can use to learn how to code in DAX.

     

    Again a huge thanks for all your help.

     

    Vishesh Jain

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

mail2vjj

 

Try this

 

First Create a CombinedTable. From the Modelling Tab>>NEW TABLE

 

CombinedTable =
UNION (
    SUMMARIZE (
        Inward,
        Inward[Date],
        Inward[Size],
        Inward[Type],
        "Quantity", SUM ( Inward[Quantity] )
    ),
    SUMMARIZE (
        Outward,
        Outward[Date],
        Outward[Size],
        OUTward[Type],
        "Quantity", - SUM ( oUTward[Quantity] )
    )
)

 

 

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    mail2vjj

     

    Then add this MEASURE in this new Table

     

    Closing_Stock =
CALCULATE (
    SUM ( CombinedTable[Quantity] ),
    FILTER (
        ALLEXCEPT ( CombinedTable, CombinedTable[Size], CombinedTable[Type] ),
        CombinedTable[Date] <= SELECTEDVALUE ( CombinedTable[Date] )
    )
)
      • mail2vjj's avatar
        mail2vjj
        Helper III
        8 years ago

        Hi Zubair_Muhammad

         

        Thank you again for helping me out with the problem.

         

        I was also working on the same lines are your proposed solution(which is quite similar to the last time you helped me out), but I created that table as a query instead.

         

        Anyways, your solution almost works but there is a flaw in it.

         

        The solution is not showing me Closing Stock of a particular Size and Type, if there is no outward for it on a particular date.

        Hence when I use a date slicer on it, it will not show me data in the Closing stock for that particular date.

         

        For eg:

        Your solution is missing the following from the Closing Stock table:

        For 2nd Jan - A, 30x30, 30

        For 3rd Jan - B, 20x20, 20

         

        If you could please somehow work this out, it will be great.

        Meanwhile I am also working on it and if I come up with a solution, I will let you know.

         

        Thank you again for your help,

         

        Vishesh Jain

    • mail2vjj's avatar
      mail2vjj
      Helper III
      8 years ago

      Another thing I am seeing in my table is that, if I take the Size and Type from their seperate respective tables, the measure starts giving wrong results.

       

      It is just adding the Closing Stock up for that Date and showing it in every single row, regardless or Type and Size.

       

       

      Again this will be a problem when I use the Type and Size in the slicer.

      I have created relationships between the new Combined Table and the Type and Size tables, but the values itself are wrong.

       

      It would be great if you can help me fix this.

       

      Thank you,

       

      Vishesh Jain