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   Date Entry Type Quantity...
  • 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

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

    • jamesdanuk's avatar
      jamesdanuk
      Frequent Visitor
      8 years ago

      Hi Alex,

       

      Apologies it should be 995, not 1005.

       

      Does that help?

       

      In regards to teh Visual i would in the report select a Reporting Date and the Report (Visual will display say bar chart with location or item ) 

       

      Thanks

       

      Dan

       

       

      • alexei7's avatar
        alexei7
        Continued Contributor
        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