Forum Discussion

jamesdanuk's avatar
jamesdanuk
Frequent Visitor
8 years ago
Solved

Stock On Hand

Hi,

 

I am using Power BI (Direct Query) from a SQL Source, and am having problems doing a Closing Stock Balance.

 

I have in a SQL Table the following structure

 

DateEntry TypeQuantityItemIDLocationID
31/05/2018Take On1000110
01/06/2018Sales-10110
01/06/2018Purchase5110

 

I am after a Closing Stock Calculation, that on 01/06/2018 displays for Item 1 and Location 10 the stock, currently when i select Quantity on 01/06/2018 it displays 5, i need it to display 1005 as to take in the closing balance from the beginning rather than limit the scope.

 

I have a Date Table also that is linked to the Date Column, as well as a Item and Location Dimension that is linked by the ID's on my stock table.

 

Thanks

 

Dan

  • alexei7's avatar
    alexei7
    8 years ago

    Hi Dan,

     

    I think something like the following might help you achieve what you want (although difficult to be certain without knowing your model):

     

    Closing Stock Balance =
    CALCULATE (
     SUM ( Table1[Quantity] ),
     FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
    )

     

    Obviously, you'll need to change any table or column names to your own.

     

    Hope that helps,

    Thanks

    Alex

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    HI jamesdanuk,

     

    You can add calculate column to replace entry type to 'stock in' and 'stock out', then remove 'data', 'entry type' and add above column to show summary amount.

     

    Regards,

    Xiaoxin Sheng

    • jamesdanuk's avatar
      jamesdanuk
      Frequent Visitor
      8 years ago

      Hi Xiaoxin,

      I am unsure what you mean? are you able to demostrate how to do this ?

       

      Thanks

       

      Dan

  • alexei7's avatar
    alexei7
    Continued Contributor
    8 years ago

    Hi Dan,

     

    Quick question - is the result you want to see 1005 or 995 (including sales)?

     

    Cheers

    Alex

    • jamesdanuk's avatar
      jamesdanuk
      Frequent Visitor
      8 years ago

      Hi Alex,

       

      I want 1005 on the 01/06/2018 and 1000 to be returned on the 31/05/2018 does that make sense?

       

      Thanks

       

      Dan

      • alexei7's avatar
        alexei7
        Continued Contributor
        8 years ago

        So sales are not included in your calculation?

         

        Also, what result would you like to see? I'm imagining a visual metric for total sales whereby you filter on one date to see "Closing Stock" for that data, but would be helpful if you can confirm

         

        Thanks

        Alex